Oracle SQL中用嵌套DECODE/CASE查询上级的上级问题

现有代码的问题 #

你当前的DECODE语句仅能返回直接上级ID，无法递归向上遍历至顶层的big boss。比如输入ID3时，只会返回直接上级4，而不是4的上级2。

最优方案：递归查询（支持任意层级） #

由于你的数据是层级结构，递归查询是最灵活的方案，无论中间有多少层级都能正确找到顶层big boss。

方式1：使用CONNECT BY（Oracle原生层级查询） #

SELECT id AS big_boss_id
FROM bosses
WHERE status = 'big boss'
START WITH id = &input_id  -- 替换为你要输入的基层负责人ID，比如3
CONNECT BY PRIOR boss_id = id;

方式2：使用WITH递归CTE（更易读的标准SQL写法） #

WITH recursive_bosses AS (
    -- 初始行：输入的基层负责人
    SELECT id, boss_id, status
    FROM bosses
    WHERE id = &input_id
    UNION ALL
    -- 递归向上找上级
    SELECT b.id, b.boss_id, b.status
    FROM bosses b
    JOIN recursive_bosses rb ON b.id = rb.boss_id
)
-- 从递归结果中筛选顶层的big boss
SELECT id AS big_boss_id
FROM recursive_bosses
WHERE status = 'big boss';

嵌套DECODE/CASE的可行性 #

仅当层级固定为3级（lil boss -> boss -> big boss）时，嵌套写法才能生效，扩展性极差（如果新增中间层级会直接失效）。示例如下：

嵌套DECODE写法 #

SELECT 
    DECODE(
        (SELECT status FROM bosses WHERE id = (SELECT boss_id FROM bosses WHERE id = &input_id)),
        'big boss', (SELECT boss_id FROM bosses WHERE id = &input_id),
        'boss', (SELECT boss_id FROM bosses WHERE id = (SELECT boss_id FROM bosses WHERE id = &input_id))
    ) AS big_boss_id
FROM dual;

CASE语句写法 #

SELECT 
    CASE 
        WHEN (SELECT status FROM bosses WHERE id = (SELECT boss_id FROM bosses WHERE id = &input_id)) = 'big boss' 
            THEN (SELECT boss_id FROM bosses WHERE id = &input_id)
        WHEN (SELECT status FROM bosses WHERE id = (SELECT boss_id FROM bosses WHERE id = &input_id)) = 'boss'
            THEN (SELECT boss_id FROM bosses WHERE id = (SELECT boss_id FROM bosses WHERE id = &input_id))
    END AS big_boss_id
FROM dual;

UNION方案的可行性 #

UNION本身是合并结果集的语法，单独使用无法实现层级遍历。若硬编码层级结合UNION，虽然能得到结果，但同样扩展性差，仅适合固定层级场景，不推荐使用。示例如下：

-- 仅适配3级层级的UNION写法
SELECT id AS big_boss_id FROM bosses WHERE id = &input_id AND status = 'big boss'
UNION
SELECT boss_id AS big_boss_id FROM bosses WHERE id = &input_id AND status = 'boss'
UNION
SELECT (SELECT boss_id FROM bosses WHERE id = b.boss_id) AS big_boss_id FROM bosses b WHERE b.id = &input_id AND b.status = 'lil boss';

内容的提问来源于stack exchange，提问作者Валентин Д.