Подсчет количества детей в иерархических данных 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

Я прочитал несколько вещей об иерархических данных, но я как-то застрял на нескольких внутренних соединениях на родительских идентификаторах. Может быть, кто-то может помочь мне здесь.

Ответ 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

Ответ 2

Рассмотрите возможность использования измененного способа обхода дерева предзаказов для хранения иерархических данных. См. http://www.sitepoint.com/hierarchical-data-database/

Определение количества детей для любого node затем становится простым:

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