Oracle 12c层级排序：父员工在前，下属按入职日期升序排列

你需要的层级排序可以通过Oracle原生的层级查询语法实现，推荐使用CONNECT BY写法，写法简单且性能更优：

SELECT 
  employer_id, 
  supervisor_employer_ref, 
  date_hired
FROM 你的表名 -- 请替换为实际表名
START WITH supervisor_employer_ref IS NULL
CONNECT BY PRIOR employer_id = supervisor_employer_ref
ORDER SIBLINGS BY date_hired ASC;

语法说明 #

• START WITH supervisor_employer_ref IS NULL：指定层级查询的根节点为无上级的顶级员工
• CONNECT BY PRIOR employer_id = supervisor_employer_ref：定义父子关联关系：父节点的员工ID等于子节点的上级ID
• ORDER SIBLINGS BY date_hired ASC：核心排序逻辑，保证同一上级的所有下属按入职时间升序排列，同时不会破坏「顶级节点在前、下属紧随其后」的层级结构

如果更习惯用标准SQL的递归CTE写法（Oracle 12c及以上版本支持），也可以用以下实现：

WITH emp_hierarchy AS (
    -- 先查询所有顶级根节点
    SELECT 
      employer_id, 
      supervisor_employer_ref, 
      date_hired,
      1 AS hierarchy_level,
      date_hired AS root_hire_date -- 记录根节点入职时间，用于排序不同顶级节点的顺序
    FROM 你的表名
    WHERE supervisor_employer_ref IS NULL
    
    UNION ALL
    
    -- 递归查询所有下属节点
    SELECT 
      e.employer_id, 
      e.supervisor_employer_ref, 
      e.date_hired,
      eh.hierarchy_level + 1 AS hierarchy_level,
      eh.root_hire_date
    FROM 你的表名 e
    JOIN emp_hierarchy eh ON e.supervisor_employer_ref = eh.employer_id
)
SELECT employer_id, supervisor_employer_ref, date_hired
FROM emp_hierarchy
ORDER BY 
  root_hire_date ASC, -- 更早入职的顶级节点排在前面
  hierarchy_level ASC, -- 父节点排在子节点前面
  date_hired ASC; -- 同层级下属按入职时间升序

注：你给出的预期输出末尾多了一条employer_id为45687的重复记录，属于输入笔误，正常执行上述SQL不会返回重复数据。

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