能否不使用CONNECT BY，借助Oracle分析函数（如DENSE_RANK）构建树结构并改写查询？

完全可以通过递归CTE结合分析函数替代CONNECT BY语法，实现HR员工表的层级查询，同时保留原查询的LEVEL字段和同级按姓氏排序的逻辑：

WITH emp_hierarchy AS (
    -- 初始化根节点（employee_id=100）
    SELECT 
        last_name,
        employee_id,
        manager_id,
        1 AS LEVEL,
        -- 生成同级排序标识，确保根节点自身的排序
        ROW_NUMBER() OVER (ORDER BY last_name) AS sibling_rank,
        -- 构建排序路径，用于最终的层级顺序控制
        '/' || last_name AS sort_path
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    -- 递归遍历子节点
    SELECT 
        e.last_name,
        e.employee_id,
        e.manager_id,
        eh.LEVEL + 1 AS LEVEL,
        -- 按父节点分组，对同级子节点按姓氏排序
        ROW_NUMBER() OVER (PARTITION BY e.manager_id ORDER BY e.last_name) AS sibling_rank,
        -- 拼接路径，保证层级内的排序继承父节点顺序
        eh.sort_path || '/' || e.last_name AS sort_path
    FROM employees e
    JOIN emp_hierarchy eh 
        ON e.manager_id = eh.employee_id
)
-- 按排序路径输出，实现`ORDER SIBLINGS BY last_name`的效果
SELECT 
    last_name,
    employee_id,
    manager_id,
    LEVEL
FROM emp_hierarchy
ORDER BY sort_path;

核心逻辑说明： #

• 递归CTE的起始部分定位根节点，递归部分通过关联父节点ID遍历所有子节点，自然生成LEVEL层级值。
• 使用ROW_NUMBER()分析函数（也可根据需求替换为DENSE_RANK()）对同一父节点下的子节点按last_name排序，模拟原查询的ORDER SIBLINGS BY行为。
• 通过拼接sort_path字段，最终按路径排序确保整个层级结构的顺序和原查询完全一致——父节点在前，同级子节点按姓氏排序，子节点的子节点紧随其后。

如果不需要路径字段，也可以用层级化的排序键替代：

WITH emp_hierarchy AS (
    SELECT 
        last_name,
        employee_id,
        manager_id,
        1 AS LEVEL,
        -- 根节点的排序键
        TO_CHAR(ROW_NUMBER() OVER (ORDER BY last_name), 'FM0000') AS sort_key
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    SELECT 
        e.last_name,
        e.employee_id,
        e.manager_id,
        eh.LEVEL + 1 AS LEVEL,
        -- 拼接父节点排序键和当前节点的同级排名，确保排序正确
        eh.sort_key || TO_CHAR(ROW_NUMBER() OVER (PARTITION BY e.manager_id ORDER BY e.last_name), 'FM0000') AS sort_key
    FROM employees e
    JOIN emp_hierarchy eh 
        ON e.manager_id = eh.employee_id
)
SELECT 
    last_name,
    employee_id,
    manager_id,
    LEVEL
FROM emp_hierarchy
ORDER BY sort_key;

这里的sort_key用固定长度的数字拼接，避免字符串排序时的字典序问题，同样能精准实现同级排序和层级结构的顺序。

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