T-SQL查询：WITH AS 递归计算某部门的所有上级机构或下级机构

IF (OBJECT_ID('DEPT') IS NOT NULL)
  DROP TABLE DEPT
CREATE TABLE DEPT(ID INT,PID INT, NAME VARCHAR(20))

INSERT INTO DEPT VALUES
(1,0,'集团'),
(2,1,'公司A'),
(3,1,'公司B'),
(4,2,'部门A1'),
(5,2,'部门A2'),
(6,2,'部门A3'),
(7,3,'部门B1'),
(8,3,'部门B2'),
(9,5,'小组A2_1'),
(10,8,'小组B2_1')

SELECT * FROM DEPT


--求一个部门的所有下级，如[部门A2] 的所有下级'
;WITH D(ID,PID,NAME,LVL)
AS(
  SELECT ID,PID,NAME,0 LVL FROM DEPT WHERE NAME='部门A2' 
  UNION ALL
  SELECT DEPT.ID,DEPT.PID,DEPT.NAME,LVL +1  
  FROM DEPT INNER JOIN D ON DEPT.PID=D.ID 
)
SELECT * FROM D

/*
ID  PID NAME    LVL
--  --- --------- ---
5 2 部门A2    0
9 5 小组A2_1  1
*/



--求一个部门的所有上级，如[部门B2] 的所有上级'
;WITH D(ID,PID,NAME,LVL)
AS(
  SELECT ID,PID,NAME,0 LVL FROM DEPT WHERE NAME='部门B2'
  UNION ALL
  SELECT DEPT.ID,DEPT.PID,DEPT.NAME,LVL +1  
  FROM DEPT INNER JOIN D ON DEPT.ID=D.PID
)
SELECT * FROM D

/*
ID  PID NAME  LVL
--  --- ------  ---
8 3 部门B2  0
3 1 公司B 1
1 0 集团  2
*/