SQLクエリを使用してテーブルからネストされた子IDを取得する方法

bugfix> sql > 投稿 2020-08-22 18:36

Table1とTable2の2つのテーブルがあります。 Table1には、ツリーから除外する必要があるSectionIDがあります。

Table2には、ParentIDとそのChildIDがあります。シナリオがありますparentIDには、別の子の親である子があります。 Table1にあるparentIDのすべてのchildIDをTable2から取得する必要があります。

例Table1には、20000543の親である20000449の親であるSectionId 20000448があります（下の画像を参照）。 2000448のすべてのchildIdとsubchildIdを取得する必要があります。

つまり、結果は2000448（Table1に存在する）にchildIds 20000449,20000450,20000452,20000543,20000544,20000490,20000739,20000740,20000741が必要です。（ここでは20000449からSubchildIDを取得しています。20000450にchildIDがある場合は、それらも考慮する必要があります。）

CTEを使用しようとしましたが、期待した結果が得られません。

どんな助けも感謝します。

;WITH Hierarchy AS 
(
    SELECT DISTINCT
        RootID = T.ParentID,
        DescendantID = T.ParentID,
        Level = 0
    FROM
        Table2 AS T
    WHERE
        NOT EXISTS (SELECT 'records without parents' FROM Table2 AS N WHERE N.ChildID = T.ParentID)
    UNION ALL
    SELECT
        RootID = H.RootID,
        DescendantID = T.ChildID,
        Level = H.Level + 1
    FROM
        Hierarchy AS H
        INNER JOIN Table2 AS T ON H.DescendantID = T.ParentID
)
SELECT
    H.RootID,
    H.DescendantID,
    H.Level
FROM
    Hierarchy AS H
ORDER BY
    H.RootID,
    H.Level

Table1 からレコードを除外するには NOT EXISTS を使用できます必要に応じて、最後または再帰で（再帰で停止すると、 Table1 からのレコードの後の子が無効になります）。

編集： サンプルデータと一時テーブルを使用するには：

IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
    DROP TABLE #Table2
CREATE TABLE #Table2 (
    ChildID INT,
    ParentID INT)
INSERT INTO #Table2 (
    ChildID,
    ParentID)
VALUES
    (20000449, 20000448),
    (20000450, 20000448),
    (20000452, 20000448),
    (20000543, 20000449),
    (20000544, 20000449),
    (20000490, 20000543),
    (20000739, 20000490),
    (20000740, 20000490),
    (20000741, 20000490)
;WITH Hierarchy AS 
(
    SELECT DISTINCT
        RootID = T.ParentID,
        DescendantID = T.ParentID,
        Level = 0
    FROM
       #Table2 AS T
    WHERE
        NOT EXISTS (SELECT 'records without parents' FROM #Table2 AS N WHERE N.ChildID = T.ParentID)
    UNION ALL
    SELECT
        RootID = H.RootID,
        DescendantID = T.ChildID,
        Level = H.Level + 1
    FROM
        Hierarchy AS H
        INNER JOIN #Table2 AS T ON H.DescendantID = T.ParentID
)
SELECT
    H.RootID,
    H.DescendantID,
    H.Level
FROM
    Hierarchy AS H
ORDER BY
    H.RootID,
    H.Level

結果：

RootID  DescendantID    Level
20000448    20000448    0
20000448    20000449    1
20000448    20000450    1
20000448    20000452    1
20000448    20000543    2
20000448    20000544    2
20000448    20000490    3
20000448    20000739    4
20000448    20000740    4
20000448    20000741    4

bugfix

SQLクエリを使用してテーブルからネストされた子IDを取得する方法

sql

sql-server

common-table-expression

関連記事