mysql中，像这样的表数据结构，如何进行sql查询更高效、易扩展？

Question

问题：

mysql查询某个部门（包括所有子集部门）下所有的员工。

graph LR
A部门 --> B部门
B部门 --> C部门
C部门 --> E部门
C部门 --> F部门
B部门 --> D部门
D部门 --> G部门

同一个员工，可以在多个部门下同时存在。如何高效地查询某部门（包括所有子部门）下，不重复的获取所有的员工。
例:
查询C部门时，需要返回[C,E,F]部门所有的员工。
查询A部门时，需要返回[A,B,C,D,E,F,G]部门所有的员工。
查询D部门时，需要返回[D,G]部门所有的员工，假设小明同学同时存在于D部门和G部门下是，获取员工列表时不能重复获取两个小明。

当前表结构

现有3个表：department（部门）、user（员工）、department_user_relate（部门员工关联表）

department表结构:

{
    id: [INT(11),PRIMARY_KEY], //唯一编号
    name: CHAR(32), //部门名称
    parent_id: [INT(11)], //上级部门编号
    org_id: [INT(11)], //所属公司（多组织）
    ...
}

user表结构:

{
    id:[INT(11),PRIMARY_KEY],// 唯一编号
    username:CHAR(32),// 昵称
    org_id:[INT(11)],//所属公司（多组织）
    ...
}

department_user_relate 表结构

{
    id:[INT(11),PRIMARY_KEY],
    dept_id:INT(11),//部门编号
    user_id:INT(11),//用户编号
}

Thanks

Answer 1

WITH RECURSIVE depts(id)
AS( 
 SELECT id FROM dept WHERE dept.id = 要查找的部门ID
 UNION ALL 
 SELECT id FROM dept 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 )
)

如果不支持cte表示式, 那只能把全部部门都查出来, 然后查关联表.

如果不支持cte格式, 需要修改部门树表的结构, 把部门的所有父ID都保存下来, 查找的时候使用 find_in_set,
或者如果所有父ID用的是json结构字段保存的话, 用 json_contains 来判断.