使用SQL Server 2005執行遞迴查詢

SQL Server 2005的新功能Common Table Expressions (CTE)可以用來作遞迴的SQL查詢

;WITH MenuCTE(MenuKey, ParentMenuKey, MenuName) AS
(
-- Anchor Query
SELECT MenuKey, ParentMenuKey, MenuName FROM Menu WHERE MenuKey = 1
UNION ALL
-- Recursive Query
SELECT m.MenuKey, m.ParentMenuKey, m.MenuName FROM Menu m INNER JOIN MenuCTE r ON m.ParentMenuKey = r.MenuKey
)

SELECT MenuKey, ParentMenuKey, MenuName FROM MenuCTE


參考資料：http://www.infoq.com/news/2007/10/CTE