Oracle使用connect by prior实现层级员工表转扁平表的相关问题

你需要的Oracle SQL语句可以通过两种方式实现，第一种为你要求的CONNECT BY PRIOR层级查询写法，第二种为固定两级上级场景下更简洁的自连接写法：

写法1：使用CONNECT BY PRIOR实现 #

SELECT 
    empID AS emp,
    empName,
    MAX(CASE WHEN lv = 1 THEN managerID END) AS subBoss,
    MAX(CASE WHEN lv = 1 THEN mgrName END) AS subBossName,
    MAX(CASE WHEN lv = 2 THEN managerID END) AS Boss,
    MAX(CASE WHEN lv = 2 THEN mgrName END) AS BossName
FROM (
    SELECT 
        CONNECT_BY_ROOT e.empID AS empID,
        CONNECT_BY_ROOT e.empName AS empName,
        m.empID AS managerID,
        m.empName AS mgrName,
        LEVEL AS lv
    FROM empTabl e
    LEFT JOIN empTabl m ON e.managerID = m.empID
    CONNECT BY PRIOR e.managerID = e.empID
    START WITH e.empID IN (SELECT empID FROM empTabl)
) t
GROUP BY empID, empName
ORDER BY empID;

写法2：两次自连接实现（固定2级上级场景更推荐） #

SELECT 
    e.empID AS emp,
    e.empName,
    m1.empID AS subBoss,
    m1.empName AS subBossName,
    m2.empID AS Boss,
    m2.empName AS BossName
FROM empTabl e
LEFT JOIN empTabl m1 ON e.managerID = m1.empID
LEFT JOIN empTabl m2 ON m1.managerID = m2.empID
ORDER BY e.empID;

两种写法执行后都可以得到你需要的输出结果。

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