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
*/