如何在Azure Synapse SQL中编写动态查询获取员工层级数据

针对Azure Synapse Analytics不支持递归CTE的限制，我们可以通过循环迭代追踪层级+动态透视的方式实现需求，以下是完整解决方案：

步骤1：计算员工的管理者层级链 #

首先通过临时表和循环，追踪每个员工的所有上级管理者，并确定需要生成的最大层级数：

-- 创建临时表存储员工层级追踪数据
DROP TABLE IF EXISTS #EmpChain;
CREATE TABLE #EmpChain (
    emp_win INT,
    current_supervisor INT,
    level_num INT -- 临时层级编号，从直接上级开始递增
);

-- 初始化数据：直接上级和无上级的顶层员工
INSERT INTO #EmpChain (emp_win, current_supervisor, level_num)
SELECT emp_win, supervisor_id, 1
FROM emp
WHERE supervisor_id IS NOT NULL AND supervisor_id != emp_win;

INSERT INTO #EmpChain (emp_win, current_supervisor, level_num)
SELECT emp_win, NULL, 0
FROM emp
WHERE supervisor_id IS NULL OR supervisor_id = emp_win;

DECLARE @max_level INT = 1;

-- 循环迭代，追踪所有上级管理者直到顶层
WHILE EXISTS (
    SELECT 1 
    FROM #EmpChain ec
    JOIN emp e ON ec.current_supervisor = e.emp_win
    WHERE e.supervisor_id IS NOT NULL AND e.supervisor_id != e.emp_win
    AND NOT EXISTS (
        SELECT 1 
        FROM #EmpChain ec2 
        WHERE ec2.emp_win = ec.emp_win 
        AND ec2.level_num = ec.level_num + 1
    )
)
BEGIN
    SET @max_level = @max_level + 1;
    
    -- 插入下一层级的管理者数据
    INSERT INTO #EmpChain (emp_win, current_supervisor, level_num)
    SELECT ec.emp_win, e.supervisor_id, ec.level_num + 1
    FROM #EmpChain ec
    JOIN emp e ON ec.current_supervisor = e.emp_win
    WHERE e.supervisor_id IS NOT NULL AND e.supervisor_id != e.emp_win
    AND NOT EXISTS (
        SELECT 1 
        FROM #EmpChain ec2 
        WHERE ec2.emp_win = ec.emp_win 
        AND ec2.level_num = ec.level_num + 1
    );
END

-- 调整层级编号：让最高层管理者对应level_0，层级越高编号越小
DROP TABLE IF EXISTS #EmpChain_Adjusted;
CREATE TABLE #EmpChain_Adjusted (
    emp_win INT,
    manager_id INT,
    adjusted_level INT
);

INSERT INTO #EmpChain_Adjusted (emp_win, manager_id, adjusted_level)
SELECT 
    ec.emp_win,
    ec.current_supervisor,
    (MAX(ec2.level_num) OVER (PARTITION BY ec.emp_win)) - ec.level_num AS adjusted_level
FROM #EmpChain ec
WHERE ec.current_supervisor IS NOT NULL;

-- 获取最终需要生成的最大层级编号
SELECT @max_level = ISNULL(MAX(adjusted_level), 0) FROM #EmpChain_Adjusted;

步骤2：动态生成透视SQL #

根据计算出的最大层级，动态生成包含所有层级管理者列的查询：

DECLARE @sql NVARCHAR(MAX);

-- 构建SELECT语句，生成每个层级的管理者列
SET @sql = N'SELECT emp_win';

DECLARE @i INT = 0;
WHILE @i <= @max_level
BEGIN
    SET @sql = @sql + N', MAX(CASE WHEN adjusted_level = ' + CAST(@i AS NVARCHAR) + N' THEN manager_id END) AS level_' + CAST(@i AS NVARCHAR) + N'_manager';
    SET @i = @i + 1;
END

-- 补充FROM和GROUP BY子句
SET @sql = @sql + N'
FROM #EmpChain_Adjusted
GROUP BY emp_win
ORDER BY emp_win;';

-- 执行动态SQL
EXEC sp_executesql @sql;

-- 清理临时表（可选）
DROP TABLE IF EXISTS #EmpChain;
DROP TABLE IF EXISTS #EmpChain_Adjusted;

关键说明 #

1. 层级命名规则：最高层管理者对应level_0_manager，下一层对应level_1_manager，以此类推，直接上级对应编号最大的列，符合“层级越高编号越小”的要求。
2. 适配Synapse限制：全程使用循环和临时表替代递归CTE，完全兼容Azure Synapse Analytics的SQL环境。
3. 动态列生成：脚本会自动根据表中最长的管理者链长度生成对应数量的列，无需手动调整。

内容的提问来源于stack exchange，提问作者SIVARAMAKRISHNAREDDY TANGIRALA