遞歸 CTE 為所有孩子找到總計

這是我想使用遞歸T-SQL查詢（大概CTE）搜尋的程序集樹，預期結果如下。我想知道給定任何零件的每個組件的總量。

這意味著如果我搜尋“鉚釘”，我想知道程序集中每個級別的總計數，而不僅僅是直接子級計數。

Assembly (id:1)
   |
   |-Rivet
   |-Rivet
   |-SubAssembly (id:2)
   |   |
   |   |-Rivet
   |   |-Bolt
   |   |-Bolt
   |   |-SubSubAssembly (id:3)
   |      |
   |      |-Rivet
   |      |-Rivet
   |
   |-SubAssembly (id:4)
      |-Rivet
      |-Bolt

   DESIRED Results
   -------
   ID, Count
   1 , 6
   2 , 3
   3 , 2
   4 , 1

目前，我可以獲得直接父母，但想知道如何擴展我的 CTE 以允許我向上滾動此資訊。

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i 
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

創建腳本

CREATE TABLE [dbo].[Instances] ( 
 [InstanceID] NVARCHAR (50) NOT NULL, 
 [Part] NVARCHAR (50) NOT NULL, 
 [ParentID] NVARCHAR (50) NOT NULL, );



INSERT INTO Instances 
Values 
 (1, 'Assembly', 0), 
 (50, 'Rivet', 1), 
 (50, 'Rivet', 1), 
 (2, 'SubAssembly', 1), 
 (50, 'Rivet', 2), 
 (51, 'Bolt', 2), 
 (51, 'Bolt', 2), 
 (3, 'SubSubAssembly', 2), 
 (50, 'Rivet', 3), 
 (50, 'Rivet', 3), 
 (4, 'SubAssembly2', 1), 
 (50, 'Rivet', 4), 
 (51, 'Bolt', 4)

此遞歸 CTE ( SQL Fiddle ) 應該適用於您的範例：

WITH cte(ParentID) AS(
   SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
   UNION ALL
   SELECT i.ParentID FROM cte c
   INNER JOIN @Instances i ON c.ParentID = i.InstanceID
   WHERE i.ParentID > 0
)
SELECT ParentID, count(*) 
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

輸出

ParentID    Count
1           6
2           3
3           2
4           1

注意：您在評論中提到該問題僅包含簡化的範例表，真實數據具有適當的索引並充分處理重複和數據。

使用的數據（SQL Fiddle）：

DECLARE @Instances TABLE( 
   [InstanceID] int NOT NULL
   , [Part] NVARCHAR (50) NOT NULL
   , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES 
   (1, 'Assembly', 0)
   , (50, 'Rivet', 1)
   , (50, 'Rivet', 1)
   , (2, 'SubAssembly', 1)
   , (50, 'Rivet', 2)
   , (51, 'Bolt', 2)
   , (51, 'Bolt', 2)
   , (3, 'SubSubAssembly', 2)
   , (50, 'Rivet', 3)
   , (50, 'Rivet', 3)
   , (4, 'SubAssembly2', 1)
   , (50, 'Rivet', 4)
   , (51, 'Bolt', 4)
;

引用自：https://dba.stackexchange.com/questions/138928