重複 - 階層的なSQLデータ内の子の数を数える




レコード数とは (2)

次のような単純なデータ構造の場合:

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

私は階層的なデータについていくつかのことを読んでいますが、どういうわけか、親IDの複数の内部結合にはまっています。 誰かが私をここで助けてくれるかもしれない。


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





hierarchical-data