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));
支援無限層級、碼簡單、效能佳(索引良好時)。
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 = 無限制(慎用,防死循環)
Father_Id = menu_id 向下遞迴。OPTION (MAXRECURSION n):預設 100,設 0 移除上限(需確保資料無循環)。Father_Id 上建立非叢集索引(含 menu_id),加速遞迴 JOIN;Path = '/1/5/18/')配合 LIKE '/1/5/18/%' 單次掃描替代遞迴;HIERARCHYID 類型,原生樹狀結構操作。