590
社区成员
发帖
与我相关
我的任务
分享
;WITH tb(ID,Name,Score,ParentID)AS(
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
),cte AS (
SELECT ID AS MainID, ID,Name,Score,ParentID FROM tb
UNION ALL
SELECT cte.MainID,tb.ID,tb.Name,tb.Score,tb.ParentID FROM tb INNER JOIN cte ON cte.ID=tb.ParentID
)
SELECT cte.MainID,COUNT(0),SUM(Score) FROM cte
GROUP BY cte.MainID
/*
MainID
----------- ----------- -----------
1 4 410
2 1 102
3 2 207
4 1 104
6 2 210
7 1 105
*/
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
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*/