SQL递归查询获取部门代码

Question

我需要生成一个用户部门代码列表。如果用户没有代码，则获取其管理器代码，依此类推。

初始表如下所示:

manager emp     code
-----------------------
boss    subboss AAA
boss    subsub  SUBCODE
subboss john    ABC
subboss alan    (null)
(null)  boss    ZZZ
subsub  steve   (null)
steve   rick    (null)
CREATE TABLE Users
(
[manager] varchar(10), 
[emp] varchar(10), 
[code] varchar(10)
);

INSERT INTO Users ([manager], [emp], [code])
VALUES
('boss', 'subboss', 'AQQ'),
('boss', 'subsub', 'SUBSUB'),
('subboss', 'john', 'ABC'),
('subboss', 'alan', null),
(null, 'boss', 'ZZZ'),
('subsub', 'steve', null),
('steve', 'rick', null);

期望的结果是:

manager emp     code
------------------------
boss    subboss AAA
boss    subsub  SUBCODE
subboss john    ABC
subboss alan    AAA
(null)  boss    ZZZ
subsub  steve   SUBCODE
steve   rick    SUBCODE

我的第一次尝试是:

select 
manager, emp,
coalesce(code, (select code from Users u1 where u.manager = u1.code))
from 
Users u;

但是它只返回直接管理器代码。

我很感激如何递归地使用CTE的技巧。

Answer 1

试试这样:

WITH Hierarchy AS
(
-- create the "anchor" - the toplevel node(s)
SELECT
u.emp, u.manager, u.code, 0 AS Level
FROM
Users u
WHERE
u.manager IS NULL
UNION ALL
-- recursive part - join subordinate to manager, one level up
SELECT
u.emp, u.manager, COALESCE(u.code, h.code), h.Level + 1
FROM
Hierarchy h
INNER JOIN
Users u ON u.manager = h.emp
)
SELECT
*
FROM
Hierarchy

结果是这样的数据集:

emp     manager code    Level
-----------------------------
boss    NULL    ZZZ      0
subboss boss    AQQ      1
subsub  boss    SUBSUB   1
steve   subsub  NULL     2
rick    steve   NULL     3
john    subboss ABC      2
alan    subboss NULL     2