计算分层SQL数据中的子代数 [英] Counting number of children in hierarchical SQL data

问题描述

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

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

我想计算每个级别的孩子人数.因此，我将获得一个新列"NoOfChildren"，如下所示:

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

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

我读了一些有关分层数据的内容，但是我不知何故陷入了parentID的多个内部联接中.也许有人可以在这里帮助我.

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.

推荐答案

使用

Using a CTE would get you what you want.

• 递归遍历所有子项，记住根.
• COUNT每个根的项目.
• JOIN再次与原始表一起产生结果.

• 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.

测试数据

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

这篇关于计算分层SQL数据中的子代数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！