ID_,NAME_,PARENT_ID_,ORDER_NO_,CODE_,GRADE_,ORG_KIND_,DEM_ID_,PATH_,PATH_NAME_,UPDATE_TIME_,IS_DELE_,VERSION_,LIMIT_NUM_,NOW_NUM_,EXCEED_LIMIT_NUM_,create_by_,create_time_,create_org_id_,update_by_,tenant_id_ SELECT * FROM uc_org where grade_='3' and is_dele_='0' and org_kind_='ogn' AND id_ IN #{id} WITH road_info AS ( SELECT CONCAT(SUBSTRING_INDEX(o.path_, '.', LENGTH(o.path_) - LENGTH(REPLACE(o.path_, '.', '')) - 1), '.', r.id_, '.') AS path_, r.id_ AS ROAD_ID_, r.name_ AS ROAD_NAME_, r.code_ AS ROAD_CODE_ FROM uc_org o JOIN biz_base_road r ON o.name_ = r.ADDRESS_ WHERE o.grade_ = '5' AND o.org_kind_ = 'dept' and o.is_dele_='0' and r.is_dele_='0' AND o.id_ IN #{id} ) SELECT o.*, o.ID_ AS ORG_ID_, ri.ROAD_ID_, ri.ROAD_NAME_, ri.ROAD_CODE_ , ri.path_ AS ROAD_PATH_ FROM uc_org o INNER JOIN road_info ri ON ri.path_ LIKE CONCAT('%', o.path_, '%') WHERE o.grade_='3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' WITH team_info AS ( SELECT CONCAT(r.NAME_, '-', t.NAME_) AS NAME_, o.path_, t.id_, r.id_ AS ROAD_ID_, r.name_ AS ROAD_NAME_, r.code_ AS ROAD_CODE_, r.sn_ FROM biz_base_road r INNER JOIN uc_org o ON o.name_ = r.ADDRESS_ AND o.grade_ = '5' AND o.org_kind_ = 'dept' AND o.is_dele_ = '0' INNER JOIN biz_base_team t ON r.id_ = t.road_id_ AND t.is_dele_ = '0' WHERE r.is_dele_ = '0' AND o.id_ IN #{id} ), member_count AS ( SELECT TEAM_ID_, count(MEMBER_ID_) AS MEMBER_NUM_ FROM biz_bind_member GROUP BY TEAM_ID_ ) SELECT o.*, ti.id_ AS TEAM_ID_, ti.NAME_ AS TEAM_NAME_, ti.ROAD_ID_, ti.ROAD_NAME_, ti.ROAD_CODE_, IFNULL(mc.MEMBER_NUM_,0) AS MEMBER_NUM_ FROM uc_org o INNER JOIN team_info ti ON ti.path_ LIKE CONCAT(o.path_, '%') AND LENGTH(ti.path_) > LENGTH(o.path_) LEFT JOIN member_count mc ON ti.id_=mc.TEAM_ID_ WHERE o.grade_ = '3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' ORDER BY ti.sn_ WITH team_info AS ( SELECT CONCAT(r.NAME_, '-', t.NAME_) AS NAME_, o.path_, t.id_, r.id_ AS ROAD_ID_, r.name_ AS ROAD_NAME_, r.code_ AS ROAD_CODE_, r.sn_ FROM biz_base_road r INNER JOIN uc_org o ON o.name_ = r.ADDRESS_ AND o.grade_ = '5' AND o.org_kind_ = 'dept' AND o.is_dele_ = '0' INNER JOIN biz_base_team t ON r.id_ = t.road_id_ AND t.is_dele_ = '0' WHERE r.is_dele_ = '0' AND t.type_ = #{type} AND o.id_ IN #{id} ) SELECT o.*, ti.id_ AS TEAM_ID_, ti.NAME_ AS TEAM_NAME_, ti.ROAD_ID_, ti.ROAD_NAME_, ti.ROAD_CODE_ FROM uc_org o INNER JOIN team_info ti ON ti.path_ LIKE CONCAT(o.path_, '%') AND LENGTH(ti.path_) > LENGTH(o.path_) WHERE o.grade_ = '3' AND o.is_dele_ = '0' AND o.org_kind_ = 'ogn' ORDER BY ti.sn_ SELECT ID_, NAME_, CODE_, GRADE_, PATH_, parent_id_, ORG_KIND_ FROM uc_org u AND GRADE_ IN ('0','1','2','3','4','5') AND id_ IN #{id}