mysql高效查询部门及其子部门员工
问题:如何高效地查询某个部门及其所有子部门下的员工,避免重复数据?
表结构:
查询方案:
WITH RECURSIVE depts(id) AS( SELECT id FROM department WHERE id = 要查找的部门ID UNION ALL SELECT id FROM department AS d where d.parent_id = id ) select * from user where user.id in ( SELECT user_id FROM department_user_relate where dept_id in ( select id from depts ) )
扩展: