用法1:第一级数据统计
use Tempdb
go
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(22),[Score] int,[ParentID] int)
Insert #T
select 1,N'战一',101,0 union all
select 2,N'战二',102,1 union all
select 3,N'战三',103,1 union all
select 4,N'战四',104,3 union all
select 6,N'战五',105,0 union all
select 7,N'战五',105,6
GO
;WITH CTET AS
(
Select *,ID2=ID,Name2=Name from #T WHERE ParentID=0
UNION ALL
SELECT a.*,b.ID2,b.Name2 FROM #T AS a INNER JOIN CTET AS b ON b.ID=a.ParentID
)
SELECT ID2,Name2,SUM(Score) AS Scroe,COUNT(*) AS con FROM CTET GROUP BY Name2,ID2
/*
ID2 Name2 Scroe con
1 战一 410 4
6 战五 210 2*/
用法二:统计每个数据
create table tbs(id varchar(3) , pid varchar(3) , name varchar(10));
insert into tbs values('001' , null , '广东省');
insert into tbs values('002' , '001' , '广州市');
insert into tbs values('003' , '001' , '深圳市') ;
insert into tbs values('004' , '002' , '天河区') ;
insert into tbs values('005' , '003' , '罗湖区');
insert into tbs values('006' , '003' , '福田区') ;
insert into tbs values('007' , '003' , '宝安区') ;
insert into tbs values('008' , '007' , '西乡镇') ;
insert into tbs values('009' , '007' , '龙华镇');
insert into tbs values('010' , '007' , '松岗镇');
select * from tbs
with t as (
select id,id q from tbs union all
select a.id,b.q from tbs a inner join t b on a.pid=b.id
)
select tbs.*,COUNT(1)-1 from t inner join tbs on t.q=tbs.id group by tbs.id,tbs.name,tbs.pid