mysql 遍历查询部门下的用户数量

Question

通过部门ID递归所有子部门，并查询员工数量和部门业绩总和，这样用过sql语句或者函数可以实现吗？
例如：sql查询部门deptid=5 输出查询结果是：
部门：渠道部
员工：30
销量：150

#部门表

#用户表

Answer 1

请说明一下mysql的版本，8之前和8之后的写法不一样

---

--测试数据
create table test_20220315_c (id int,name VARCHAR(20),pid int);
insert into test_20220315_c values (1,'a',0);
insert into test_20220315_c values (2,'b',1);
insert into test_20220315_c values (4,'c',1);
insert into test_20220315_c values (5,'d',1);
insert into test_20220315_c values (6,'e',1);
insert into test_20220315_c values (11,'f',5);
insert into test_20220315_c values (12,'g',5);
insert into test_20220315_c values (13,'h',5);
insert into test_20220315_c values (21,'i',11);
insert into test_20220315_c values (22,'j',11);

create table test_20220315_d (uid varchar(20),uname varchar(20),did varchar(20),amount int);
insert into test_20220315_d values ('zhangsan','zs','21',20);
insert into test_20220315_d values ('lisi','ls','21,4',20);

--查询sql
select count(uid),sum(amount) from 
(SELECT  DATA.id FROM( 
SELECT 
@ids as _ids, 
( SELECT @ids := GROUP_CONCAT(id) 
FROM test_20220315_c 
WHERE FIND_IN_SET(pid, @ids) 
) as cids, 
@l := @l+1 as level 
FROM test_20220315_c, 
(SELECT @ids :=5, @l := 0 ) b  /* 条件 5 在这里 */
WHERE @ids IS NOT NULL 
) id, test_20220315_c DATA 
WHERE FIND_IN_SET(DATA.id, _ids)) x,
test_20220315_d d
where FIND_IN_SET(x.id,d.did)

以上sql参考自 https://www.oschina.net/question/2402835_2285575

但不确定你人员信息里面的多个部门，是否可能在一个检索分支里？这样的话可能会被统计多次

---

如果存在一个人在多个检索分支里，那就查员工表，把部门当存在条件，下面这个sql就这个意思，但可以减少括号层数的，你自己看着改改吧

select count(uid),sum(amount) from test_20220315_d d
where exists ( select 1 from 
(SELECT  DATA.id FROM( 
SELECT 
@ids as _ids, 
( SELECT @ids := GROUP_CONCAT(id) 
FROM test_20220315_c 
WHERE FIND_IN_SET(pid, @ids) 
) as cids, 
@l := @l+1 as level 
FROM test_20220315_c, 
(SELECT @ids :=5, @l := 0 ) b 
WHERE @ids IS NOT NULL 
) id, test_20220315_c DATA 
WHERE FIND_IN_SET(DATA.id, _ids)) x 
where  FIND_IN_SET(x.id,d.did))