Целое число детей в иерархических данных SQL

Question

Целое число детей в иерархических данных SQL

для простой структуры данных такой как как:

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. Возможно, кто-то мог помочь мне здесь.

21

sql sql-server hierarchy hierarchical-data

задан Dennis G 14 June 2013 в 07:26

1 ответ

Другие вопросы по тегам:

sql sql-server hierarchy hierarchical-data

Похожие вопросы:

score 25 · Accepted Answer

Использование CTE даст вам то, что вы хочу.

Рекурсивно перебрать всех дочерних элементов, запомнив корень.
COUNT элементов для каждого корня.
СОЕДИНИТЕ их снова с исходной таблицей, чтобы получить результаты.

Тестовые данные

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