萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 遞迴查詢父子層級:四層內以 CTE 替代硬編碼 IN 巢狀查詢

原始硬編碼寫法(僅支援 4 層、難維護)

SELECT menu_id, menu_name, Father_Id
FROM Sys_Menu
WHERE menu_id = '18'
   OR Father_Id = 18
   OR Father_Id IN (SELECT menu_id FROM Sys_Menu WHERE menu_id = '18' OR Father_Id = 18)
   OR Father_Id IN (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 (Common Table Expression)

支援無限層級、碼簡單、效能佳(索引良好時)。

WITH RecursiveCTE AS (
  -- Anchor: 根節點
  SELECT menu_id, menu_name, Father_Id, 1 AS Level
  FROM Sys_Menu
  WHERE menu_id = 18

  UNION ALL

  -- Recursive: 子孫節點
  SELECT m.menu_id, m.menu_name, m.Father_Id, r.Level + 1
  FROM Sys_Menu m
  INNER JOIN RecursiveCTE r ON m.Father_Id = r.menu_id
  WHERE r.Level < 100 -- 防無限遞迴,MaxRecursion 預設 100
)
SELECT menu_id, menu_name, Father_Id, Level
FROM RecursiveCTE
OPTION (MAXRECURSION 0); -- 0 = 無限制(慎用,防死循環)

關鍵點解析

  • Anchor Member:起始集合(此為單一根節點,也可多根)。
  • Recursive Member:透過 Father_Id = menu_id 向下遞迴。
  • Level 欄位:追蹤深度,便於篩選/顯示縮排。
  • OPTION (MAXRECURSION n):預設 100,設 0 移除上限(需確保資料無循環)。

效能建議

  • 在 Father_Id 上建立非叢集索引(含 menu_id),加速遞迴 JOIN;
  • 大量資料考慮 路徑編碼法(如 Path = '/1/5/18/')配合 LIKE '/1/5/18/%' 單次掃描替代遞迴;
  • SQL Server 2008+ 支援 HIERARCHYID 類型,原生樹狀結構操作。
copyright © 萬盛學電腦網 all rights reserved