如何基于CONNECT BY层级查询结果生成自动编号大纲

实现核心逻辑是结合ROW_NUMBER()窗口函数给同级节点排序编号，再用SYS_CONNECT_BY_PATH拼接各层级编号即可得到目标格式的outline值。

示例实现SQL #

假设你的源树表结构为t_tree(id, parent_id, col1)，根节点parent_id为null，查询语句如下：

SELECT 
  LEVEL AS "level",
  LTRIM(SYS_CONNECT_BY_PATH(同级序号, '.'), '.') AS outline,
  col1
FROM (
  -- 先给每个节点计算其在同级父节点下的排序序号
  SELECT 
    id,
    parent_id,
    col1,
    ROW_NUMBER() OVER(PARTITION BY parent_id ORDER BY id) AS 同级序号
  FROM t_tree
)
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id;

逻辑说明 #

• 内层子查询用ROW_NUMBER()开窗，按父节点parent_id分组，给同一个父节点下的所有子节点按排序规则生成连续序号，你可以根据实际需求调整ORDER BY的字段，比如按col1排序也可以
• 外层递归查询用SYS_CONNECT_BY_PATH把从根节点到当前节点的所有层级的序号用.拼接，最后用LTRIM去掉开头多余的.就得到了你要的层级编号
• 必须加ORDER SIBLINGS BY保证同级节点的排序和你要的编号顺序一致，避免编号乱序
• 如果你的原始查询已经有过滤条件，可以直接放到内层子查询里，不影响序号计算

样例运行结果 #

完全匹配你需要的输出格式：

level outline col1
----- ------- --------
1     1       text1
1     2       text2
2     2.1     text3
3     2.1.1   text4
3     2.1.2   text5
1     3       text6

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