SQL Server 遞迴查詢父子層級資料：四層 CTE 與 IN 子查詢寫法

需求：給定父節點 ID，查詢其所有後代節點（子、孫、曾孫、玄孫共四層）。資料表 Sys_Menu 欄位：menu_id、menu_name、Father_Id。

方法一：巢狀 IN 子查詢（固定層數、可讀性佳）

SELECT menu_id, menu_name, Father_Id
FROM Sys_Menu
WHERE menu_id = '18'                          -- 第 0 層：自己
   OR Father_Id = 18                          -- 第 1 層：直接子節點
   OR Father_Id IN (                          -- 第 2 層：孫節點
        SELECT menu_id FROM Sys_Menu 
        WHERE menu_id = '18' OR Father_Id = 18
   )
   OR Father_Id IN (                          -- 第 3 層：曾孫節點
        SELECT menu_id FROM Sys_Menu 
        WHERE Father_Id IN (
            SELECT menu_id FROM Sys_Menu 
            WHERE menu_id = '18' OR Father_Id = 18
        )
   );

優點：邏輯直觀、無需遞迴 CTE 支援 (SQL 2000 相容)。
缺點：層數固定、深度超過四層需手動擴展、效能隨層數指數級下降。

方法二：遞迴 CTE (SQL Server 2005+ 推薦)

WITH MenuTree AS (
    -- Anchor: 根節點
    SELECT menu_id, menu_name, Father_Id, 0 AS Level
    FROM Sys_Menu
    WHERE menu_id = '18'
    
    UNION ALL
    
    -- Recursive: 子節點
    SELECT m.menu_id, m.menu_name, m.Father_Id, mt.Level + 1
    FROM Sys_Menu m
    INNER JOIN MenuTree mt ON m.Father_Id = mt.menu_id
    WHERE mt.Level < 4  -- 限制深度
)
SELECT menu_id, menu_name, Father_Id, Level
FROM MenuTree
ORDER BY Level, menu_id;

優點：支援任意深度、單次掃描、可輸出層級欄位、效能穩定。
缺點：需 SQL 2005+、需設定 MAXRECURSION 防無限遞迴。

效能建議

• 在 Father_Id 建立非叢集索引：CREATE INDEX IX_Sys_Menu_FatherId ON Sys_Menu(Father_Id);
• 大量資料時，CTE 版本通常優於巢狀 IN。
• 若頻繁查詢整棵樹，考慮路徑枚舉 (path 欄位存 '1/5/18/...') 或 巢狀集合 (lft/rgt) 模型。

適用場景

• 選單/目錄樹狀結構查詢
• 組織架構下屬遞迴
• BOM 物料清單展開
• 分類目錄全路徑列舉

依資料庫版本與樹深度選擇巢狀 IN (淺層/舊版) 或遞迴 CTE (深層/新版)，並務必索引外鍵欄位。