SQL Server中遞歸自連線如何有效率地檢索分層資料？

在 SQL Server 中使用遞歸自連接高效檢索分層資料

SQL Server 的遞歸自連線提供了一種在單一表格中導覽分層資料結構的有效方法。 此技術涉及遍歷表的行以根據層次關係收集相關資料。 以下查詢示範了一個簡化的方法：

<code class="language-sql">WITH q AS (
    SELECT *
    FROM mytable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q;</code>

此查詢使用名為「q」的遞迴公用表格運算式 (CTE)。 CTE 先選擇 ParentID 為 NULL 的所有行，代表層次結構中的頂層節點。 隨後，它遞歸地將 mytable 與 CTE 本身連接起來，根據 ParentID 和 PersonID 欄位新增子行。這個迭代過程一直持續到所有後代都被包括在內。

考慮這個範例mytable：

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 = 2) 將會回傳：

PersonID	Initials	ParentID
2	EB	1
4	SW	2

為了維持層次結構順序，包含排序條件的修改查詢是有益的：

<code class="language-sql">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;</code>

此增強型查詢向 CTE 引入了 bc 列，表示從每個節點到根的路徑。 依 bc 對結果進行排序可確保保留分層結構（包括同級順序）。 可以自訂 ORDER BY 子句以根據需要調整兄弟排序。

以上是SQL Server中遞歸自連線如何有效率地檢索分層資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！