sql - 计算 GUID 列的所有子项

Question

我想要一个特定父母所在的所有孩子和子孩子的计数。

我有这个数据：

 |                     Id                  | Parent 
 +-----------------------------------------+-------------------------------------+  
 |  736F8C6A-D58D-442E-BE2E-3B9F0595C58B   | NULL                                |
 |  CA828BBA-6657-46FC-BA26-7ED8C7FB220C   | 736F8C6A-D58D-442E-BE2E-3B9F0595C58B|
 |  2DB8A8F9-9F29-4F3A-907F-A6ACEDE12859   | 736F8C6A-D58D-442E-BE2E-3B9F0595C58B|

如果我通过736F8C6A-D58D-442E-BE2E-3B9F0595C58B了，那么它应该返回其子孙的总数——总共是 2。

有什么建议么？

标签： sqlsql-servertsqlrecursioncommon-table-expression

Answer 1

首先，我使用以下查询来创建临时表

CREATE TABLE #TBLTEMP(ID int, ParentID int)

INSERT INTO #TBLTEMP(ID,ParentID)
VALUES(1,NULL)
,(2,1)
,(3,2)
,(4,2)
,(5,3)
,(6,NULL)
,(7,6)
,(8,NULL)
,(9,NULL)

然后我使用以下查询来检索孩子的数量

WITH CTE_1(ParentID) AS(
    SELECT ParentID FROM #TBLTEMP T1
    UNION ALL
    SELECT T2.ParentID FROM CTE_1 c
    INNER JOIN #TBLTEMP T2 ON c.ParentID = T2.ID
    WHERE T2.ParentID > 0
), CTE_2 AS ( SELECT ID , 0 as cnt FROM #TBLTEMP T3 WHERE NOT EXISTS (SELECT 1 FROM CTE_1 c2 where c2.ParentID = T3.ID))
SELECT ParentID, count(*) as cnt 
FROM CTE_1
WHERE ParentID IS NOT NULL
GROUP BY ParentID
UNION
SELECT ID as ParentID, cnt FROM CTE_2

ORDER BY ParentID

结果是：

参考

• 递归 CTE 为所有孩子找到总计

更新 1

要获取特定 ID 的计数：

WITH CTE_1(ParentID) AS(
    SELECT ParentID FROM #TBLTEMP T1
    UNION ALL
    SELECT T2.ParentID FROM CTE_1 c
    INNER JOIN #TBLTEMP T2 ON c.ParentID = T2.ID
    WHERE T2.ParentID > 0
), CTE_2 AS ( SELECT ID , 0 as cnt FROM #TBLTEMP T3 WHERE NOT EXISTS (SELECT 1 FROM CTE_1 c2 where c2.ParentID = T3.ID)), CTE_3 AS(
SELECT ParentID, count(*) as cnt 
FROM CTE_1
WHERE ParentID IS NOT NULL
GROUP BY ParentID
UNION
SELECT ID as ParentID, cnt FROM CTE_2) 
SELECT cnt FROM CTE_3 WHERE ParentID = 1