/*
部门人员统计-涉及到树形目录统计
*/
begin tran
--创建数据测试环境
create table #bm(部门ID int,部门名称 varchar(10),上级部门ID int)
create table #ry(人员ID int identity(1,1),所属部门ID int)
insert into #bm
select 1,'A部门',0
union all select 10,'A-1部门',1
union all select 11,'A-2部门',1
union all select 12,'A-2部门',1
union all select 101,'A-1-1部门',10
union all select 102,'A-1-2部门',10
union all select 103,'A-1-3部门',10
union all select 111,'A-2-1部门',11
union all select 112,'A-2-2部门',11
union all select 113,'A-2-3部门',11
union all select 121,'A-3-1部门',12
union all select 122,'A-3-2部门',12
union all select 123,'A-3-3部门',12
insert into #ry
select 1
union all select 1
union all select 10
union all select 10
union all select 10
union all select 11
union all select 11
union all select 12
union all select 101
union all select 102
union all select 102
union all select 103
union all select 111
union all select 111
union all select 121
union all select 121
union all select 122
union all select 123
union all select 123
--生成指定部门及其所有下属部门的临时表,这里是:部门id=1,如果统计其他部门,更改:部门id=你要统计的部门id
select cast(部门id as varchar(1000)) as 部门编号累计, 部门id,部门名称 into #bmtemp
from #bm where 部门id=1
while exists(select 1 from #bm
where 上级部门id in (select 部门id from #bmtemp)
and 部门id not in(select 部门id from #bmtemp))
insert into #bmtemp
select (select 部门编号累计 from #bmtemp where 部门id=a.上级部门id)
+'-'+cast(部门id as varchar)
,部门id,部门名称 from #bm a
where 上级部门id in (select 部门id from #bmtemp)
and 部门id not in(select 部门id from #bmtemp)
--得到人数统计的结果
select 部门id,部门名称,count(人员id) as 本部门人员合计
,(select count(人员id) from #ry where 所属部门ID in (select 部门id from #bmtemp where 部门编号累计 like a.部门编号累计+'%' )) as 本部门人员总计
from #bmtemp a,#ry b
where a.部门id=b.所属部门ID
group by a.部门id,部门名称,a.部门编号累计
order by a.部门id
select a.部门ID,count(b.人员ID ) from 部门 a join 人员 b on a.部门ID=b.所属部门ID
where a.部门ID in (select id from @temp) group by a.部门ID
上述语句则对每部门的人数分别进行总计,而并没有将子部门的人数进行合计,咳,我的语文不行了,举个例子(数据中存在的数据)
部门名称 人数
部门1, 5
子部门10,11,12 各6人
第三级部门101,102,103 各7人
111,112,113,
121,122,123,(每个子部门有三个三级部门)
进行这样的统计 部门1关心下级部门的人数,结果应该是
部门1, 5
子部门 10, 21+6=27
11, 27
12 27
哥们如何去做啊
while exists (
select id from 部门
where 上级部门ID in (select id from @temp)
and 部门ID not in (select id from @temp)
)
insert @temp
select id from 部门
where 上级部门ID in (select id from @temp)
and 部门ID not in (select id from @temp)
---把所有子部门ID插入@temp
select a.部门ID,count(b.人员ID )
from 部门 a join 人员 b on a.部门ID=b.所属部门ID
where a.部门ID in (select id from @temp)
group by a.部门ID