Oracle 层级查询中实现组内排序

🧭 摘要

在 Oracle 中使用 START WITH ... CONNECT BY PRIOR 可以方便地进行层级（树形）查询。 如果想在每个层级中对子节点进行排序，可以结合 ORDER SIBLINGS BY 实现组内排序。本文通过示例演示如何在层级查询中控制同级节点的顺序。

🧩 一、基础语法

SELECT
    t.id,
    t.parent_id,
    t.name
FROM table_name t
START WITH t.parent_id IS NULL       -- 根节点
CONNECT BY PRIOR t.id = t.parent_id  -- 递归关系
ORDER SIBLINGS BY t.sort_no ASC;     -- 同级节点排序

📘 关键字说明

🧱 二、示例数据

CREATE TABLE menu (
    id NUMBER,
    parent_id NUMBER,
    name VARCHAR2(50),
    sort_no NUMBER
);

INSERT INTO menu VALUES (1, NULL, '系统管理', 1);
INSERT INTO menu VALUES (2, NULL, '内容管理', 2);
INSERT INTO menu VALUES (3, 1, '用户管理', 2);
INSERT INTO menu VALUES (4, 1, '角色管理', 1);
INSERT INTO menu VALUES (5, 2, '文章管理', 1);
INSERT INTO menu VALUES (6, 2, '评论管理', 2);
COMMIT;

🧮 三、查询层级并组内排序

SELECT
    LPAD(' ', (LEVEL - 1) * 2) || name AS tree_name,
    id,
    parent_id,
    sort_no,
    LEVEL
FROM menu
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY sort_no ASC;

🧾 查询结果

| tree_name | id | parent_id | sort_no | level | | --------- | -- | --------- | ------- | ----- | | 系统管理 | 1 | NULL | 1 | 1 | | 角色管理 | 4 | 1 | 1 | 2 | | 用户管理 | 3 | 1 | 2 | 2 | | 内容管理 | 2 | NULL | 2 | 1 | | 文章管理 | 5 | 2 | 1 | 2 | | 评论管理 | 6 | 2 | 2 | 2 |

🔹 说明：

• 同级节点（即相同 parent_id）按照 sort_no 升序排列；
• 层级关系由 CONNECT BY 自动展开；
• ORDER SIBLINGS BY 实现了“组内排序”的效果。

🌲 四、显示完整路径与层级信息

可以利用 SYS_CONNECT_BY_PATH、LEVEL、CONNECT_BY_ISLEAF 来生成树的路径和层级信息：

SELECT
    SYS_CONNECT_BY_PATH(name, '/') AS full_path,
    LEVEL AS tree_level,
    CONNECT_BY_ISLEAF AS is_leaf,
    id,
    parent_id,
    sort_no
FROM menu
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY sort_no;

输出结果

⚙️ 五、注意事项

1. ✅ ORDER SIBLINGS BY 只能用于 CONNECT BY 查询 普通查询使用 ORDER BY，但层级查询必须用 ORDER SIBLINGS BY 来实现“同级排序”。

2. ⚡ 索引优化 给 id 和 parent_id 建索引可以显著提升层级查询性能。

3. 🧩 多排序字段 例如：

   ORDER SIBLINGS BY sort_no ASC, name ASC
   

🧠 六、总结

✅ 实战技巧

• 想要输出缩进的层级结构，用 LPAD(' ', (LEVEL-1)*2)；
• 想要路径信息，用 SYS_CONNECT_BY_PATH；
• 想要组内排序，用 ORDER SIBLINGS BY。