在分层SQL数据中计算子节点数量

Question

对于一个简单的数据结构，如下所示：

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

参考一下，层次树如下所示：

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"的新列，如下所示：

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

我读了一些有关分层数据的东西，但是我在使用多个 parentIDs 进行内联时遇到了一些问题。也许有人可以在这里帮助我。

- Dennis G

---

你的层次结构树与你的输入不匹配。 - Lieven Keersmaekers

你的输出似乎与你的层次结构不匹配<g>。从查看你的层次结构来看，我会认为ID 4和7没有子级。 - Lieven Keersmaekers

你是完全正确的，层次树和输出都搞错了，我会修复的。 - Dennis G

Answer 1

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

- Lieven Keersmaekers

---

定义公共表达式的WITH子句可以与定义表提示的WITH子句混用。如果不是批处理中的第一个语句，则应该用分号显式地将前者与后者分隔开。 - Quassnoi

@moontear，养成一直将分号编码与WITH命令相邻的习惯，例如：;WITH，你就不会遇到问题了（就像@Lieven的代码一样）。 - KM.

PostgreSQL无法识别RootID。请问需要做哪些更改才能在PostgreSQL中运行？ - Rodrigo

当数据量过大时，语句会被终止。在语句完成之前，最大递归次数100已经耗尽。 - Mehdi Daustany

@MehdiDaustany - 您可以添加以下查询提示以超过默认递归级别 SELECT ... FROM ... OPTION (MAXRECURSION 200)。 - Lieven Keersmaekers

Answer 2

考虑使用修改过的先序遍历树存储分层数据。请参阅：http://www.sitepoint.com/hierarchical-data-database/ 对于任何节点确定其子节点数量变得简单，只需要：

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

- mxsscott

---

你为我节省了很多时间。对我来说是最好的答案 :) - andrzej1_1

这个系统很棒！不幸的是，对我来说不可用，因为我必须使用一个已经建立的系统，但我一定会记住它，为新的开发做准备。 - Manuel Hoffmann