34,590
社区成员
发帖
与我相关
我的任务
分享
-->创建测试环境
DECLARE @t TABLE
(
FullDept VARCHAR(20), --部门全称
Dept VARCHAR(20), --部门
ParentDept VARCHAR(20), --上级部门
Supervisor VARCHAR(20) --部门主管
)
INSERT @t SELECT 'S-IT' ,'IT','S' ,'Peter'
UNION ALL SELECT 'S-IT-CN' ,'CN','S-IT' ,'Mary'
UNION ALL SELECT 'S-IT-CN-SH' ,'SH','S-IT-CN' ,'Jack'
UNION ALL SELECT 'S-FS-AP' ,'AP','S-FS' ,'Colin'
UNION ALL SELECT 'S-FS' ,'FS','S' ,'Jerry'
UNION ALL SELECT 'S' ,'0' ,'0' ,'CiCi'
/*
SELECT * FROM @t
--------------------
FullDept(部门全称) Dept(部门) ParentDept(上级部门) Supervisor(部门主管)
-------------------- -------------------- -------------------- --------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi
(6 行受影响)
*/
-->2.使用CTE迭代返回指定子项的父项查询:
--->例1.查询部门主管为"Jack"的所有上级部门清单
;WITH t AS
(
SELECT *,0 as lvl FROM @t WHERE Supervisor = 'Jack'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 3
S-IT IT S Peter 2
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 0
(4 行受影响)
*/
--->例2.查询部门主管为"Colin"的所有上级部门清单
;WITH t AS
(
SELECT *,0 as lvl FROM @t WHERE Supervisor = 'Colin'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept )
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 2
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 0
(3 行受影响)
*/