[解決済み] 再帰的自己結合の最も簡単な方法とは？

質問

SQL Server で再帰的自己接合を行う最も簡単な方法は何ですか？私はこのようなテーブルを持っています。

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2
5          YT         NULL
6          IS         5

そして、特定の人物から始まる階層に関連するレコードのみを取得できるようにしたいのです。ですから、もし私がCJの階層をPersonID=1でリクエストした場合、私は以下を得るでしょう。

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2

そしてEBの方には

PersonID | Initials | ParentID
2          EB         1
4          SW         2

私はこれに少し行き詰っています。多くの結合に基づく固定深度の応答とは別に、これを行う方法を考えることができません。私たちは多くのレベルを持たないので、これはそのまま行うことができますが、私はそれを適切に行いたいと思います。

ありがとうございます。クリスです。

どのように解決するのですか？

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

順序付け条件を追加することで、木の順序を保持することができます。

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

を変更することで ORDER BY の条件を変更することで、兄弟の順序を変更することができます。