一、查询当前部门下的所有子部门

WITH dept
AS ( SELECT *
FROM dbo.deptTab --部门表
WHERE pid = @id
UNION ALL
SELECT d.*
FROM dbo.deptTab d
INNER JOIN dept ON d.pid = dept.id
)
SELECT *
FROM dept

二、查询当前部门所有上级部门

WITH tab
AS ( SELECT DepId ,
ParentId ,
DepName ,
[Enable] ,
0 AS [Level]
FROM deptTab WITH ( NOLOCK ) --表名
WHERE [Enable] = 1
AND depId = @depId
UNION ALL
SELECT b.DepId ,
b.ParentId ,
b.DepName ,
b.[Enable] ,
a.[Level] + 1
FROM tab a ,