sql查询部门下的所有子部门（mybatis用法）

select @pids := 6 6 为所要查询部门的id

select id,
       department_name
from (
         select t1.id,
                t1.department_name,
                if(find_in_set(parent_department_id, @pids) > 0,
                   @pids := concat(@pids, ',', id),
                   0) as ischild
         from (
                  select id,
                         parent_department_id,
                         department_name
                  from t_department t
                  order by parent_department_id,
                           id) t1,
              (select @pids := 6) t2) t3
where ischild != 0

mybatis应用

    <select id="selectChildIdsByParentDepartmentId" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from (
                 select <include refid="Base_Column_List"/>,
                        CASE
                            WHEN find_in_set(parent_department_id, @pids) > 0 THEN @pids := concat(@pids, ',', id)
                            ELSE 0
                            END as ischild
                 from (
                          select <include refid="Base_Column_List"/>
                          from t_department t
                          order by parent_department_id,id) t1,
                      (select  @pids := #{parentDepartmentId, jdbcType=BIGINT}) t2) t3
        where ischild != 0 AND is_deleted = 0
    </select>

作用： 不需要递归查询每个部门下的id，一次查出性能优化