前阵子接到一个组织架构报表的需求要在一张表里把公司部门层级从顶层一直展开到底层还要带上“这个部门在第几层”的信息。一开始我想着用程序循环递归查结果写了几十行临时代码调了半天性能还很别扭。后来突然想起来 Oracle 的层次查询里有个伪列 LEVEL一条 SQL 就能把层级数算清楚顺带把展开、缩进、路径全部搞定。从那之后我就养成了习惯凡是树形结构的检索先问自己一句——能不能用伪列 LEVEL 解决。这篇就是围绕 LEVEL 的完整总结适合正在用或者准备接触 Oracle 层次查询的开发者。我会从最基础的用法讲到行生成器这种隐藏玩法再讲几个实战里最容易翻车的地方最后用一个项目重构案例收尾。1. 一次报表需求引出的伪列LEVEL 到底解决了什么问题1.1 手工拼层级的狼狈现场先说那个报表。表结构很简单典型的邻接表模型CREATE TABLE org ( org_id NUMBER PRIMARY KEY, parent_id NUMBER, -- 父部门ID根节点的值为 NULL org_name VARCHAR2(100) );我的第一反应是写一个递归函数逐层去查子部门。伪代码大概是这样的先查出所有根部门然后对每个根部门再查子部门再对每个子部门查孙部门……每一层都要拼接缩进字符串还要自己统计深度。部门浅的时候还好一旦超过五层代码里全是重复的循环变量一多就乱。当时脑子里也闪过“用 CONNECT BY”的念头但真正促使我下决心的是连续收到两条反馈第一页面要求展示出完整的部门层级路径比如“集团 / 产品部 / 前端组”第二某些部门下面挂了三个层级需要按深度排序。手工递归要同时处理缩进、路径、排序代码量直接失控。于是我把那几十行临时代码全部删掉换成了 LEVEL。1.2 LEVEL 在层次查询里的定位LEVEL 是 Oracle 层次查询的专用伪列它表示当前行在遍历结果中的深度。注意我说的是“遍历结果”而不是“全公司原始树”这一点后面会展开讲。最基本的用法是配合 CONNECT BY 使用SELECT org_id, org_name, LEVEL FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id;查询结果里顶层部门 LEVEL 为 1它的直接子部门 LEVEL 为 2孙部门为 3依此类推。为什么这个伪列重要因为大多数业务报表不会只满足于“列出来”它还要“有层级感”。有了 LEVEL你就可以用LPAD做缩进、用LEVEL做缩进量计算、用WHERE LEVEL 3限制显示前几层、用GROUP BY LEVEL做分层统计。以前这些逻辑全要在业务代码里硬算现在数据库一次帮你算好。不过理解 LEVEL 不能只看这一层。很多人把它当成“行号”“序号”来用这也没错但更准确地说LEVEL 是当前行在递归过程中距离起始节点的深度。起始节点由START WITH决定起始节点的 LEVEL 永远是 1。2. 目录树查询起始层级、缩进和路径一次讲透2.1 从根节点出发和从任意节点出发最常见的查询是从根节点出发。上面那个例子就是START WITH parent_id IS NULL然后CONNECT BY PRIOR org_id parent_id从根一路往子节点方向扩展。但实际业务里不是每次都从根开始。比如用户登录后只需要看自己所在的部门怎么办直接把START WITH改成当前部门 ID 就行SELECT org_id, org_name, LEVEL FROM org START WITH org_id 1024 CONNECT BY PRIOR org_id parent_id;这时候 1024 这个部门就是 LEVEL 1它下面的是 LEVEL 2。千万不要以为“这个部门在原树里是第三层所以 LEVEL 应该从 3 开始”。不是的LEVEL 永远从你选定的起点重新数。如果你需要同时知道“起点是谁”和“当前节点是谁”可以用CONNECT_BY_ROOT(org_id)拿到本次递归的根节点也可以配合SYS_CONNECT_BY_PATH拿到完整路径。这两个函数和 LEVEL 是绝配后面会详细展开。2.2 缩进与路径展示LPAD 和 SYS_CONNECT_BY_PATH有了 LEVEL前端展示树的缩进就非常简单。在 SQL 里直接把缩进做出来省得前端再递归渲染SELECT LPAD( , 2 * (LEVEL - 1)) || org_name AS display_name, LEVEL FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id;2 * (LEVEL - 1)这个写法可以理解为第一层不缩进LEVEL1 时是 0第二层缩进两个空格第三层缩进四个空格。缩进的空格数可以根据 UI 需要调整本质就是“层级数乘单位宽度”。这种写法比CASE WHEN LEVEL 1 THEN ... WHEN LEVEL 2 THEN ...优雅得多而且部门再多一层也不用改 SQL。如果要做面包屑路径用SYS_CONNECT_BY_PATHSELECT org_id, org_name, LEVEL, SYS_CONNECT_BY_PATH(org_name, / ) AS full_path FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id;结果里会出现类似集团 / 产品部 / 前端组的路径。注意这个函数返回的是字符串最后一个参数是分隔符分隔符前后可以加空格。做菜单权限、组织架构导出的时候这一个函数顶得上后端一堆字符串拼接。2.3 别小看排序优先用 ORDER SIBLINGS BY我记得第一次写层次查询的时候习惯性地在最后加了ORDER BY LEVEL结果树形结构一下子全乱了。原因是普通ORDER BY是全局排序会彻底打乱父子和兄弟之间的原始遍历顺序。正确的做法是用ORDER SIBLINGS BYSELECT org_id, org_name, LEVEL FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id ORDER SIBLINGS BY org_id;这个名字很有意思“siblings”就是兄弟节点。它的语义是在遍历树的过程中只对同一父节点下的兄弟节点排序同时保持父子层级关系不变。这样既能按照部门编号或自定义排序字段排好兄弟顺序又不会破坏树的整体结构。如果你在企业项目里看到某个树查询结果顺序“看起来没问题但是不稳定”多半就是没用ORDER SIBLINGS BY或者用了普通ORDER BY恰好数据长得像有序而已。数据一变问题立刻暴露。3. 隐藏大招当行生成器用的 LEVEL3.1 用 LEVEL 生成连续日期序列很多朋友以为 LEVEL 只能用在有父子关系的表上其实它还有一个非常高频的用法行生成器。利用CONNECT BY LEVEL N可以快速产出一批连续数字再配合日期运算就能生成日期序列。例如生成当前月份每一天SELECT TRUNC(SYSDATE, MM) LEVEL - 1 AS day FROM dual CONNECT BY LEVEL EXTRACT(DAY FROM LAST_DAY(SYSDATE));这里的核心逻辑是dual表只有一行CONNECT BY LEVEL N会让这一行不断递归直到 LEVEL 超过 N从而生成 N 行数据。每行对应一个数字TRUNC(SYSDATE, MM)拿到本月第一天加上LEVEL - 1就是当月的每一天。这种写法在生成报表的“连续时间轴”时特别有用。比如按天统计用户登录数但有些日期没有数据直接用这张日期序列做左连接空白的日期也能填充出 0省得多写一堆循环。3.2 用 LEVEL 做字符串拆行行生成器还能用来拆分逗号分隔的字符串。假设有个字段值是甲,乙,丙,丁我需要把它拆成四行可以用SELECT LEVEL, REGEXP_SUBSTR(甲,乙,丙,丁, [^,], 1, LEVEL) AS part FROM dual CONNECT BY LEVEL REGEXP_COUNT(甲,乙,丙,丁, ,) 1;REGEXP_COUNT算出逗号数量加 1 就是元素个数。REGEXP_SUBSTR中的第三个参数1表示从第一个字符开始找第四个参数用LEVEL正好逐个取第 1、第 2、第 3、第 4 段。这个技巧在处理“传参过来了逗号字符串”或者“配置表里存了多个值”的场景时非常顺手。比如前端把多个选中 ID 拼成1,2,3传给后端后端可以直接用这个方式展开成行再和业务表做关联。3.3 生成测试数据的一个实用组合做性能测试或者造数据的时候LEVEL 也相当能打。比如我要往账户表灌 1000 条不重复的测试数据INSERT INTO account (account_id, created_time) SELECT LEVEL, SYSDATE - LEVEL FROM dual CONNECT BY LEVEL 1000;account_id用 LEVEL 直接生成 1 到 1000created_time用SYSDATE - LEVEL模拟过去几天的时间非常自然。如果你需要随机值可以再套一层DBMS_RANDOM.VALUE配合 LEVEL 控制行数写起来比递归 CTE 还要简洁。理解这一节之后再回看 LEVEL它其实不只是“层级深度”而是一个可以按需产生序列的递归计数器。把这一点记在脑子里再看很多 SQL思路会完全不同。4. 五个最容易翻车的细节层级数、方向、行数翻倍4.1 根的 LEVEL 是 1不是 0这是我见过最多的理解偏差。很多从其他递归算法转过来的开发者习惯从 0 开始计数于是理所当然地认为根节点 LEVEL 是 0。实际在 Oracle 里START WITH选中的根节点 LEVEL 就是 1。这个区别直接影响业务口径。比如做前端缩进时LEVEL - 1是缩进量做“层级编码”时第一层就是 01不是 00。如果你假设成 0那么所有层级展示都会偏移一层。排查这类问题的时候最简单的办法是先把LEVEL直接 select 出来肉眼核对不要凭感觉判断。另外还有一个坑如果START WITH筛选出的根节点不止一个每个根节点的 LEVEL 都从 1 开始。你会发现结果里出现两个 LEVEL1 的节点这完全正常因为它们是两棵独立的子树。4.2 从子节点往根节点追方向表达式别写反正着遍历大家都好理解CONNECT BY PRIOR org_id parent_id意思是“当前行作为父节点根据 org_id 去找 parent_id 等于它的子节点”。也就是从父到子。但有时候需求反过来给一个子部门 ID要向上找所有祖先。这时候的写法不是随便换一下表名就行而是要把PRIOR放到另一边SELECT org_id, org_name, LEVEL FROM org START WITH org_id 1024 CONNECT BY PRIOR parent_id org_id;看到区别了吗CONNECT BY PRIOR parent_id org_id表示当前行的 parent_id 是上一行的 org_id也就是从子节点往父节点方向回溯。LEVEL 依然从 1 开始只不过这次代表的是“从当前子节点往上数到第几层”不是树里的绝对深度。我个人的记忆技巧是PRIOR 放在哪一列前面就代表哪一列是“上一行”的列。要往子节点走上一行的 id 是当前行的 parent_id所以PRIOR org_id parent_id要往父节点走上一行的 parent_id 是当前行的 id所以PRIOR parent_id org_id。把这句话写在脚本注释里两个月后回头改 SQL 也不会晕。4.3 多行表直接接 LEVEL 导致的行数放大这是行生成器最容易踩的雷。如果你写成这样SELECT emp_id, LEVEL FROM emp CONNECT BY LEVEL 5;你预期可能得到 5 行但实际结果是emp 表的每一行都递归生成了 5 行总行数是emp 行数 × 5。因为CONNECT BY的递归起点实际上是 FROM 子句返回的每一行没有显式的父子条件时它会对每一行独立执行LEVEL递增。正确的方式一般有两种一是像我前面写的在dual上生成序列再和业务表做连接二是如果你确实要基于某个维度展开一定要想清楚连接条件。不要图省事直接FROM 业务表 CONNECT BY LEVEL N否则行数放大得莫名其妙。4.4 WHERE 与 CONNECT BY 条件的适用范围层次查询里过滤条件写在哪里效果完全不同。WHERE是在整个树遍历完成之后过滤CONNECT BY条件是在递归扩展时决定“要不要往下走”。举个例子。我要排除“研发部”这个部门同时不想要它的任何子部门-- 正确在 CONNECT BY 里剪枝 SELECT org_id, org_name, LEVEL FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id AND org_name ! 研发部;如果我把org_name ! 研发部写到 WHERE 里结果是研发部自身不显示但研发部下面的子部门仍然会显示出来而且这些子部门的层级路径数据还是存在的。这两者的展示效果差别很大具体用哪一种要看业务需求是不看“研发部及其子孙”还是只看“不显示研发部但保留子部门”。另外如果只想展示前两层可以写WHERE LEVEL 2也可以写CONNECT BY LEVEL 2但语义和性能上略有差异。我的习惯是对层级深度做硬限制时写在 CONNECT BY 里让它更早停止递归对某一行做条件过滤时写在 WHERE 里。强行混用容易把逻辑搞乱。4.5 循环依赖要用 NOCYCLE 兜底树形表如果数据维护不规范可能会发生循环A 的父节点是 BB 的父节点又是 A。普通 CONNECT BY 遇到这种数据会一直递归下去直到系统资源耗尽。解决办法很简单在CONNECT BY后面加NOCYCLESELECT org_id, org_name, LEVEL, CONNECT_BY_ISCYCLE AS is_cycle FROM org START WITH parent_id IS NULL CONNECT BY NOCYCLE PRIOR org_id parent_id;配合CONNECT_BY_ISCYCLE你还能直接标记出哪一行触发了循环。这个处理看起来只是加个关键字但在实时性要求高的业务里能救命。我曾经在一个权限树的数据修复场景中用这条 SQL 快速定位了十几个互相引用的脏数据效率远高于在应用层查一遍。5. 一个真实的重构案例应用层递归改为 SQL 层级查询5.1 原来的 Java 递归问题前段日子接手了一个权限菜单项目查询当前用户可见菜单的逻辑写在 Java 里。代码结构大致是先查用户的所有根菜单然后用一个递归方法反复执行SELECT * FROM menu WHERE parent_menu_id ?每次拿到子菜单再往下钻。这个方案有三个痛点。第一访问数据库次数太多菜单层级多或者用户权限大时一次页面请求可能触发几十次查询第二递归逻辑里要手工维护层级深度和排序改起来容易影响其他功能第三菜单表有 sort_no 排序字段递归时每次都要先排序再拼接代码又臭又长。我当时接手后的第一反应不是优化 Java 方法而是问了一个问题这段逻辑能不能整体搬到 SQL 里5.2 替换后的 SQL 实现与口径菜单表结构是经典的menu_id / parent_menu_id / menu_name / sort_no根节点parent_menu_id 0。替换后的核心 SQL 是这样的SELECT menu_id, menu_name, LEVEL AS menu_level, SYS_CONNECT_BY_PATH(menu_name, ) AS menu_path FROM menu START WITH parent_menu_id 0 CONNECT BY PRIOR menu_id parent_menu_id ORDER SIBLINGS BY sort_no;和原来几十行 Java 相比这段 SQL 直接把层级深度、完整路径、兄弟排序全部搞定了。Java 那边只需要执行一次查询然后按照menu_level生成树形结构或者直接用menu_path做展示。还有个细节菜单可能被多个角色引用所以实际项目里是先用角色关联查出menu_id集合再用WHERE menu_id IN (...)过滤。这种情况下仍然可以用层次查询但要注意把过滤条件放在正确位置具体区别参考 4.4 节。5.3 性能对比与这一段踩过的坑重构前后我做了简单对比原来递归方式对同一个用户生成完整菜单树需要执行 20 到 40 次查询平均耗时约 300 毫秒换成单条层级查询后耗时压到了 20 毫秒左右。性能提升主要不是因为 SQL 比 Java 快而是因为把几十次数据库往返压缩成了一次。网络开销减少之后效果立竿见影。这个过程中踩了一个不算坑但值得说的点菜单 ID 集合很大时IN列表会变得非常长超过 1000 个会直接报错。我的做法是先把菜单 ID 写入临时表再用JOIN关联避免列表长度限制也让执行计划更稳定。另一个点是ORDER SIBLINGS BY中的排序字段是sort_no但有些菜单的sort_no是空的需要提前用NVL给默认值否则排序顺序会不符合预期。经过这次重构我彻底改变了对树形查询的编码习惯。能在 SQL 里用一条语句完成的事情就不应该在应用层写递归循环。6. 回头再看我自己会怎么用 LEVEL三个体会6.1 先定方向再写 PRIOR凡是写CONNECT BY我现在的固定流程是先问“我要从父到子还是从子到父”然后在脚本注释里写清楚方向。因为 PRIOR 放在等号哪一侧决定了递归方向一旦写反结果不是报错而是数据不对排查起来特别容易懵。6.2 需要行号列表时优先想到 LEVEL不只是树形结构。只要 SQL 里需要一个连续的整数序列、日期序列、或者把字符串拆成多行我第一个想到的就是CONNECT BY LEVEL N。它比手写递归 CTE 更简洁而且 Oracle 优化器对这套写法的支持已经很成熟日常场景基本不用担心性能。6.3 别忘了 CONNECT_BY_ROOT 这条捷径如果要按“根节点”做汇总比如统计每个顶级部门下的所有人头数CONNECT_BY_ROOT比任何自连接都直观SELECT CONNECT_BY_ROOT(org_id) AS root_org_id, COUNT(*) AS member_count FROM org START WITH parent_id IS NULL CONNECT BY PRIOR org_id parent_id GROUP BY CONNECT_BY_ROOT(org_id);LEVEL 帮助你找到“我在第几层”CONNECT_BY_ROOT 帮你记住“我是从哪一棵树来的”SYS_CONNECT_BY_PATH 帮你保留“我这一路是怎么走来的”。这三个配合起来绝大多数树形报表需求都够用了。最后再分享一个小技巧遇到调试不清的层次查询先把LEVEL和SYS_CONNECT_BY_PATH直接 select 出来看一遍中间结果对每个节点的深度和来源有个直观印象再决定怎么过滤。这样做比对着空表想逻辑要快得多。LEVEL 不是个复杂的东西但把它用到位确实能让 SQL 的档次上一个台阶。