declare @temp table (id char(8))insert @temp values ('上级部门的ID值')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
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
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货