计算分层SQL数据中的子代数

使用 CTE可以满足您的需求。

• 递归地遍历所有孩子，记住根。

• COUNT

  每个根的项目。

• JOIN

  这些再次与您的原始表一起产生结果。

测试数据

DECLARE @Data TABLE (  ID INTEGER PRIMARY KEY  , ParentID INTEGER  , Text VARCHAr(32)  , Price INTEGER)INSERT INTO @Data  SELECt 1, Null, 'Root', NULL  UNIOn ALL SELECT 2, 1, 'Flowers', NULL  UNIOn ALL SELECT 3, 1, 'Electro', NULL  UNIOn ALL SELECT 4, 2, 'Rose', 10  UNIOn ALL SELECT 5, 2, 'Violet', 5  UNIOn ALL SELECT 6, 4, 'Red Rose', 12  UNIOn ALL SELECT 7, 3, 'Television', 100  UNIOn ALL SELECT 8, 3, 'Radio', 70  UNIOn ALL SELECT 9, 8, 'Webradio', 90

SQL语句

;WITH ChildrenCTE AS (  SELECT  RootID = ID, ID  FROM    @Data  UNIOn ALL  SELECt  cte.RootID, d.ID  FROM    ChildrenCTE cte          INNER JOIN @Data d ON d.ParentID = cte.ID)SELECt  d.ID, d.ParentID, d.Text, d.Price, cnt.ChildrenFROM    @Data d        INNER JOIN (          SELECt  ID = RootID, Children = COUNT(*) - 1          FROM    ChildrenCTE          GROUP BY RootID        ) cnt ON cnt.ID = d.ID