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

for a simple data structure such as so:

对于简单的数据结构，例如：

ID    parentID    Text        Price
1                 Root
2     1           Flowers
3     1           Electro
4     2           Rose        10
5     2           Violet      5
6     4           Red Rose    12
7     3           Television  100
8     3           Radio       70
9     8           Webradio    90

For reference, the hierarchy tree looks like this:

作为参考，层次结构树如下所示：

ID    Text        Price
1     Root
|2    Flowers
|-4   Rose        10
| |-6 Red Rose    12
|-5   Violet      5
|3    Electro
|-7   Television  100
|-8   Radio       70
  |-9 Webradio    90

I'd like to count the number of children per level. So I would get a new column "NoOfChildren" like so:

我想计算每个级别的孩子数量。所以我会得到一个新的专栏“NoOfChildren”，如下所示：

ID    parentID    Text        Price  NoOfChildren
1                 Root               8
2     1           Flowers            3
3     1           Electro            3
4     2           Rose        10     1
5     2           Violet      5      0
6     4           Red Rose    12     0
7     3           Television  100    0
8     3           Radio       70     1
9     8           Webradio    90     0

I read a few things about hierarchical data, but I somehow get stuck on the multiple inner joins on the parentIDs. Maybe someone could help me out here.

我读了一些关于分层数据的事情，但我不知何故被困在parentID上的多个内连接上。也许有人可以帮助我。

2 个解决方案

#1

Using a CTE would get you what you want.

使用CTE可以得到你想要的东西。

Recursively go through all children, remembering the root.
递归地遍历所有孩子，记住根。
COUNT the items for each root.
为每个根计算项目。
JOIN these again with your original table to produce the results.
再次使用原始表格连接这些以生成结果。

Test Data

测试数据

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 Statement

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.Children
FROM    @Data d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

#2

Consider using a modified preorder tree traversal way of storing the hierarchical data. See http://www.sitepoint.com/hierarchical-data-database/

考虑使用修改的预订树遍历存储分层数据的方式。请访问http://www.sitepoint.com/hierarchical-data-database/

Determining number of children for any node then becomes a simple:

确定任何节点的子节点数然后变得简单：

SELECT (right-left-1) / 2 AS num_children FROM ...

#1