部门人员统计（将下属部门人数汇总于一级部门）

最新推荐文章于 2022-08-26 18:03:27 发布

原创最新推荐文章于 2022-08-26 18:03:27 发布 · 4.4k 阅读

0 ·

CC 4.0 BY-SA版权

表结构说明：

Dept表：

DeptID DeptName ParentDeptID

1 宜都市 0

2 农业局 1

3 纪委监察局 1

4 农业综合执法大队 2

5 农业技术推广中心 2

6 特产技术推广中心 2

7 党风室 3

8 干部室 3

Dept_User_Connection表：

CID DeptID UserID

1 2 1

2 3 2

3 4 3

4 7 4

5 8 5

Users表：

UserID UserName

1 张三

2 李四

3 王五

4 甲六

5 丙丁

需求说明：要求分组汇总 "宜都市" 所有下级部门人数总和。期望输出结果如下：

DeptID DeptName DeptUserCount

2 农业局 2

3 纪委监察局 3

解题思路：

1.查询 “农业局“ 及递归查询其所有下属部门；

with cte as
(
    select DeptID,ParentDeptID,DeptName,0 as lvl from Dept
    where DeptID=2
    union all
    select d.DeptID,d.ParentDeptID,d.DeptName,lvl+1 from cte c
    inner join Dept d on c.DeptID =d.ParentDeptID
)
select * from cte

2.创建标量函数，返回 ”农业局“ 关联所有的人员总数；

CREATE FUNCTION GetDeptUserCount
(
@DeptID int
)
RETURNS int
AS
BEGIN
DECLARE @targetData VARCHAR(100);
with cte as
(
  select DeptID,ParentDeptID,DeptName,0 as lvl from Dept
  where DeptID=@DeptID
  union all
  select d.DeptID,d.ParentDeptID,d.DeptName,lvl+1 from cte c
  inner join Dept d on c.DeptID =d.ParentDeptID
)
select @targetData=COALESCE(COUNT(Connection.IN_USER_ID),0) from cte
INNER JOIN Dept_User_Connection Connection ON Connection.IN_DEPT_ID=cte.IN_DEPT_ID
INNER JOIN Users ON Connection.IN_USER_ID = Users.IN_USER_ID
RETURN @targetData ;
END