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,一次查出性能优化