40 lines
874 B
SQL
40 lines
874 B
SQL
WITH result (Id, ParentId, Category, Num)
|
||
AS
|
||
(
|
||
SELECT 1, 0, '分类1', 0
|
||
UNION ALL
|
||
SELECT 2, 1, '分类1-1',10
|
||
UNION ALL
|
||
SELECT 3, 1, '分类1-2', 10
|
||
UNION ALL
|
||
SELECT 4, 3, '分类1-2-1', 5
|
||
UNION ALL
|
||
SELECT 5, 0, '分类2', 5
|
||
UNION ALL
|
||
SELECT 6, 0, '分类3', 5
|
||
UNION ALL
|
||
SELECT 7, 6, '分类3-1', 5
|
||
UNION ALL
|
||
SELECT 8, 4, '分类1-2-1-1', 5
|
||
),
|
||
|
||
CTE(Id, ParentId, Category, Path, Num)
|
||
As
|
||
(
|
||
SELECT A.Id, A.ParentId, A.Category, CAST(A.Id As VARCHAR(MAX))+'->',A.Num
|
||
FROM result A
|
||
WHERE A.ParentId = 0
|
||
UNION ALL
|
||
SELECT B.Id, B.ParentId, B.Category,C.Path+CAST(B.Id As VARCHAR(MAX)) + '->', B.Num
|
||
FROM result B
|
||
INNER JOIN CTE c on C.Id = B.ParentId
|
||
)
|
||
--SELECT * FROM CTE
|
||
SELECT C.Id,
|
||
C.Category,
|
||
SUM(C1.Num) AS Num
|
||
FROM CTE AS C
|
||
--整个统计方法的核心就是这一句代码：CHARINDEX
|
||
INNER JOIN CTE AS C1 ON CHARINDEX(C.Path, C1.Path) = 1
|
||
GROUP BY C.ID,C.Category
|