如何在 SQL Server 中使用遞歸自連線檢索類別及其祖先？

在 SQL Server 中使用遞歸自連線來擷取類別及其祖先

本文示範如何使用遞歸自連線從 SQL Server 中的分層表中擷取類別及其祖先類別。 我們將使用通用表表達式 (CTE) 來完成此任務。

場景：

考慮一個 Categories 表，其中 Id、Name 和 ParentId 欄位表示分層類別結構。 目標是編寫一個查詢，以單一逗號分隔的字串傳回特定類別（例如「商務筆記型電腦」）及其所有父類別。

遞歸 CTE 解：

此解決方案採用遞歸 CTE 來遍歷層次結構。 這是查詢：

WITH CategoryHierarchy AS (
    SELECT id, name, CAST(name AS VARCHAR(MAX)) AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL  -- Start with root categories
    UNION ALL
    SELECT c.id, c.name, CAST(ch.path + ',' + c.name AS VARCHAR(MAX)), c.parent_id
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)

此 CTE CategoryHierarchy 遞歸地將 Categories 表與其自身連接起來。初始的 SELECT 語句選擇根類別（parent_id 為 NULL 的類別）。 UNION ALL 將其與後續的遞歸選擇結合，透過連接父類別名稱和子類別名稱來建構 path 字串。

擷取特定類別和祖先的查詢：

要擷取「商務筆記型電腦」類別及其祖先：

SELECT id, name, path
FROM CategoryHierarchy
WHERE name = 'Business Laptops';

範例表格與資料：

讓我們建立一個範例 Categories 表並插入一些資料：

CREATE TABLE Categories (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentId INT REFERENCES Categories(Id)
);

INSERT INTO Categories (Id, Name, ParentId)
VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Business Laptops', 2),
(5, 'Gaming Laptops', 2);

預期結果：

針對「商務筆記型電腦」的查詢，預期輸出為：

<code>id      name                path
4       Business Laptops     Electronics,Laptops,Business Laptops</code>

這種方法使用遞歸 CTE 有效地檢索類別及其完整譜系，為在 SQL Server 中導航分層資料提供了清晰高效的解決方案。 如果您的類別名稱可能超出此限制，請記住調整 VARCHAR(MAX) 長度。

以上是如何在 SQL Server 中使用遞歸自連線檢索類別及其祖先？的詳細內容。更多資訊請關注PHP中文網其他相關文章！