数据表结构与记录如下所示:
|
现要得到下面的结果,如何用SQL语句实现:
部门 数量
A 2
B 3
即得到上级部门的出文数量(注意,出文数量包括上级出文数量与下级出文数量。),通过上面的记录可以分析得到。希望各位大虾帮忙下,谢谢了。
with dept(id,name,p_id) as ( 注:SQL Server 2005 以上才支持递归CTE。
select 1,'A',0 union all
select 2,'B',0 union all
select 3,'C',2 union all
select 4,'D',2
) ,
files(dept_id,name) as (
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd' union all
select 1,'aa'
),
--> start here
tmp as (
select d.id,d.name,d.p_id,COUNT(*) cnt
from dept d,files f
where d.id=f.dept_id
group by d.id,d.name,d.p_id
) ,
cte as (
select 0 lvl,name root_name,id,name,p_id,cnt
from tmp where p_id=0 --
union all
select lvl+1 lvl,c.name root_name,t.id,t.name,t.p_id,t.cnt cnt
from cte c,tmp t
where c.id=t.p_id
)
select root_name,SUM(cnt) cnt
from cte
group by root_name
root_name cnt
--------- -----------
A 2
B 3