Oracle数据库：20万条数据下无需CONNECT BY PRIOR获取人员三级主管方法咨询

嗨，我来帮你搞定这个需求！其实CONNECT BY PRIOR并没有你想象的那么难，而且针对你的三级主管查询场景，它反而很高效。不过我也会给你另一种更直观的写法，你可以根据自己的习惯选择。

方法一：用Oracle原生层级查询（CONNECT BY） #

这是Oracle专门为层级关系设计的语法，20万条数据的规模下性能有保障，写法也很清晰：

WITH hierarchy_data AS (
    SELECT 
        CONNECT_BY_ROOT person_number AS person,
        supervisor_number AS supervisor,
        LEVEL AS level_num
    FROM your_table_name -- 替换成你的实际表名
    CONNECT BY NOCYCLE PRIOR supervisor_number = person_number
    -- NOCYCLE用来避免循环层级（比如A管B，B管A），如果确定没有循环可以去掉
    START WITH person_number IS NOT NULL
    AND LEVEL <= 3 -- 只取到三级主管
)
SELECT 
    person,
    MAX(CASE WHEN level_num = 1 THEN supervisor END) AS LVL1_SUP,
    MAX(CASE WHEN level_num = 2 THEN supervisor END) AS LVL2_SUP,
    MAX(CASE WHEN level_num = 3 THEN supervisor END) AS LVL3_SUP
FROM hierarchy_data
GROUP BY person
ORDER BY person;

代码解释： #

• CONNECT_BY_ROOT：获取当前层级链最底层的员工编号（也就是我们要查询的主体）
• LEVEL：标记当前是第几级主管（1是直接主管，2是主管的主管，以此类推）
• 最后用CASE和MAX把多行的层级数据转成你需要的列格式，没有对应层级的主管会显示NULL

方法二：三次自连接（更直观） #

如果你觉得层级查询语法有点绕，也可以用三次左连接的方式，写法更直白，容易理解：

SELECT 
    e.person_number AS PERSON,
    e1.supervisor_number AS LVL1_SUP,
    e2.supervisor_number AS LVL2_SUP,
    e3.supervisor_number AS LVL3_SUP
FROM your_table_name e
LEFT JOIN your_table_name e1 ON e.supervisor_number = e1.person_number
LEFT JOIN your_table_name e2 ON e1.supervisor_number = e2.person_number
LEFT JOIN your_table_name e3 ON e2.supervisor_number = e3.person_number
ORDER BY e.person_number;

注意事项： #

• 这种写法虽然简单，但如果表数据量大，一定要确保person_number和supervisor_number这两列有索引，否则查询性能可能会打折扣
• 如果某个员工没有三级主管（比如只有直接主管），对应的列会显示NULL，符合你的预期

两种方法对比： #

• 层级查询（CONNECT BY）：Oracle原生优化，大数据量下性能更优，适合复杂层级场景，哪怕以后要扩展到更多层级也容易修改
• 自连接：写法直观，容易上手，但扩展到更多层级时需要增加更多JOIN，代码会变冗长，性能也会下降

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