时间戳

首页 > 解决方案 > SQL递归查询获取部门代码

sql - SQL递归查询获取部门代码

问题描述

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

初始表如下所示：

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 递归执行此操作的提示。

标签： sqlsql-serverrecursioncommon-table-expressionhierarchy

解决方案

尝试这样的事情：

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

推荐阅读