--树递归查询,根遍历子(由外到里递归)
WITH MenuTree 
AS
(
SELECT * FROM dbo.HR_SYS_Menu WHERE UpCode = 5
UNION ALL
SELECT dbo.HR_SYS_Menu.* FROM MenuTree
JOIN dbo.HR_SYS_Menu ON MenuTree.ID = dbo.HR_SYS_Menu.UpCode
)
SELECT * FROM MenuTree
GO

--树递归查询,子遍历根(由里到外递归)
WITH MenuTree 
AS
(
SELECT * FROM dbo.HR_SYS_Menu WHERE ID = 521
UNION ALL
SELECT dbo.HR_SYS_Menu.* FROM MenuTree
JOIN dbo.HR_SYS_Menu ON MenuTree.UpCode = dbo.HR_SYS_Menu.ID
)
SELECT * FROM MenuTree

--树递归查询,子遍历根(由里到外递归)
WITH MenuTree
AS
(
SELECT A.ID,A.UpCode,A.Name FROM dbo.HR_SYS_Menu A WHERE ID = 521
UNION ALL
SELECT dbo.HR_SYS_Menu.ID,dbo.HR_SYS_Menu.UpCode,dbo.HR_SYS_Menu.Name FROM MenuTree B
JOIN dbo.HR_SYS_Menu ON B.UpCode = dbo.HR_SYS_Menu.ID
)
SELECT * FROM MenuTree
GO
--树递归查询,根遍历子(由外到里递归)
WITH MenuTree
AS
(
SELECT A.ID,A.UpCode,A.Name FROM dbo.HR_SYS_Menu A WHERE A.UpCode = 5
UNION ALL
SELECT dbo.HR_SYS_Menu.ID,dbo.HR_SYS_Menu.UpCode,dbo.HR_SYS_Menu.Name FROM MenuTree B
JOIN dbo.HR_SYS_Menu ON B.ID = dbo.HR_SYS_Menu.UpCode
)
SELECT * FROM MenuTree
GO

SQL CTE树递归查询