如何在SQL中实现递归Case语句生成员工层级sort_key

需求说明 #

需要给员工表添加sort_key列，规则如下：

• 无上级（Supervisor_ID为NULL）的员工，sort_key为0
• 其余员工的sort_key为其直属上级的sort_key + 1

示例数据（已包含目标sort_key）：

employee_ID first_name  last_name   Supervisor_ID   sort_key
580         Vick        White       583             3
585         Jonathan    Brown       580             4
465         Jordan      Mistry      585             5
627         Rogan       Jacob       465             6
628         Tad         Max         465             6
583         Logan       Fi          81              2
81          Fara        Jake        8               1
8           Raj         Xhi         NULL            0

你尝试用CASE语句实现，但无法处理Else部分的递归逻辑：

Case
When supervisor_id = NULL then 0
Else
(...) + 1
End as sort_key

解决方案：递归CTE #

单一CASE语句无法实现这个需求，因为sort_key是层级递归依赖的，必须从顶层（无上级）员工开始，逐层向下计算下级的层级。这里用递归公共表表达式（CTE）是标准解决方案。

完整SQL代码 #

WITH EmployeeHierarchy AS (
    -- 锚点：筛选出无上级的顶层员工，sort_key设为0
    SELECT 
        employee_ID,
        first_name,
        last_name,
        Supervisor_ID,
        0 AS sort_key
    FROM employees
    WHERE Supervisor_ID IS NULL  -- 注意：SQL中判断NULL必须用IS NULL，不能用= NULL

    UNION ALL

    -- 递归：关联上级数据，计算当前员工的sort_key
    SELECT 
        e.employee_ID,
        e.first_name,
        e.last_name,
        e.Supervisor_ID,
        eh.sort_key + 1 AS sort_key
    FROM employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.Supervisor_ID = eh.employee_ID
)
-- 查询最终结果，可按sort_key排序查看层级
SELECT * FROM EmployeeHierarchy ORDER BY sort_key, employee_ID;

关键说明 #

1. NULL判断：SQL中不能用supervisor_id = NULL，必须用supervisor_id IS NULL，因为NULL不参与常规的相等比较。
2. 递归逻辑：锚点成员先获取所有顶层员工，递归成员通过JOIN关联已计算出的上级数据，将上级的sort_key加1作为当前员工的层级。
3. 兼容性：该方法适用于支持递归CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+等）。

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