SQL Server 2005 遞迴 CTE 實作控制流程圖（CFG）儲存與支配關係查詢

場景：關聯資料庫儲存圖狀結構

控制流程圖（CFG）本質為有向圖，節點代表基本區塊，邊代表控制流向。關聯表自然表達：

CREATE TABLE CfgEdges (
    NodeId   CHAR(10) NOT NULL,   -- 當前節點
    ParentId CHAR(10) NOT NULL,   -- 父節點（支配者）
    PRIMARY KEY (NodeId, ParentId)
);

需求：查詢指定節點的所有遞迴支配者（所有祖先）

輸入：`NodeId = 'a'` → 輸出：`b, c, d`（假設 a→b→c→d 鏈）。

遞迴 CTE 解決方案 (SQL Server 2005+)

WITH DomCTE (NodeId, ParentId, Lvl) AS (
    -- Anchor member：起始節點
    SELECT NodeId, ParentId, 1 AS Lvl
    FROM CfgEdges
    WHERE NodeId = 'a'

    UNION ALL

    -- Recursive member：向上追溯父節點
    SELECT e.NodeId, e.ParentId, c.Lvl + 1
    FROM CfgEdges e
    INNER JOIN DomCTE c ON e.NodeId = c.ParentId
)
SELECT NodeId, ParentId, Lvl
FROM DomCTE
OPTION (MAXRECURSION 1000); -- 防無限遞迴，預設 100

執行邏輯解析

1. 分割：CTE 分為定位點成員與遞迴成員。
2. 初始化：執行定位點查詢，產生基礎結果集 T0（節點 a 的直接父節點）。
3. 遞迴：以 Ti 為輸入，執行遞迴成員產生 Ti+1（上一層父節點的父節點）。
4. 終止：直到遞迴成員回傳空集合。
5. 合併：`UNION ALL` 合併 T0 到 Tn 為最終結果。

查詢結果範例

關鍵提醒

• `MAXRECURSION` 選項防止無限迴圈（循環圖），0 表示無限制（慎用）。
• 遞迴 CTE 僅支援單一遞迴參考，複雜圖運算（如傳遞閉包、最短路徑）需考慮 CLR 整合或圖資料庫。
• 效能：針對 `NodeId`、`ParentId` 建立索引；大圖考慮物化路徑或嵌套集合模型。

延伸應用

• 組織架構（員工-主管）全鏈查詢
• 物料清單 (BOM) 展開
• 分類目錄無限層級穿透
• 帳務流水追溯