做后台管理系统的开发几乎都绕不开树形结构的SQL而说到树形查询就绕不开伪列LEVEL。我第一次处理组织架构树的时候用的是应用层for循环一层层查数据库五千个部门直接让接口卡了快两秒被前端同事委婉提醒了好几次。后来改造成一条带LEVEL伪列的SQL整个查询变成一次数据库往返性能问题当场解决。从那以后凡是遇到父子层级、目录树、递归结构的场景我都会先想想能不能用LEVEL直接在SQL里搞定。这篇内容主要围绕伪列LEVEL的应用展开适合后端开发、数据开发、数据分析师以及所有经常跟树形数据打交道的人。你看完可以快速上手写出树形查询也能避开我踩过的不少坑。先说清楚什么是伪列LEVEL为什么它能简化树形查询然后给出完整的实操案例、常用场景、问题排查和方案选型经验。1. 先搞清楚伪列LEVEL到底是什么用在哪儿1.1 伪列不是普通字段但你可以把它当字段用在数据库里伪列是查询阶段才出现的“逻辑字段”它不像建表时定义的字段那样存在物理存储里但你可以直接SELECT出来也可以参与WHERE、ORDER BY运算。常见的伪列有ROWNUM、ROWID而LEVEL是专属于层次查询也叫递归查询的伪列。LEVEL的作用非常直白表示当前行在树形结构中的深度。根节点所在行的LEVEL值是1根节点的子节点是2孙节点是3依此类推。你可以把它理解成家族谱里的“辈分”太爷爷是第1辈爷爷是第2辈爸爸是第3辈你是第4辈。每次往下走一层LEVEL就加1。LEVEL本身不存任何业务字段但它在树形查询里几乎是万能辅助用它做缩进、做层级编号、做前缀路径甚至控制递归深度。1.2 没有LEVEL之前树形查询有多痛苦很多人在接触LEVEL之前处理树都是这样干的在应用层写一个递归方法先查根节点再根据parent_id循环查子节点俗称N1查询。或者把全表数据一次性查出来放到内存里用Map组装成树。数据量大一点还要考虑递归深度、死循环、接口超时。这些方案不是不能用但都有明显的痛点。N1查询对数据库压力大网络往返次数多内存组装虽然一次查出全表但需要维护递归逻辑而且不支持分批查询、分页查询经常还会写出一堆自己看着都头疼的树组装代码。而SQL层的层次查询让数据库引擎直接帮你做深度遍历你只需要声明“从哪个节点开始、父子关系怎么连接”数据库会自动按树的顺序返回结果LEVEL会伴随每一行出现。这就是LEVEL存在的核心价值用声明式的写法替代过程式的递归逻辑。一句话总结伪列LEVEL是SQL做树形查询的基本工具理解了它你的层次查询就从“怎么拼代码”变成“怎么描述关系”。2. 从零写一个树形查询START WITH与CONNECT BY的实操拆解2.1 第一个案例三行SQL拉出整棵分类树以最常见的商品分类为例。假设有一张分类表category结构如下CREATE TABLE category ( id NUMBER PRIMARY KEY, parent_id NUMBER, name VARCHAR2(50) );模拟数据大概是这种id为1的是“图书”parent_id为空id为2的是“文学”parent_id为1id为3的是“小说”parent_id为2id为4的是“计算机”parent_id为1。也就是说图书下面挂着文学和计算机文学下面再挂小说。想把这棵树完整查出来只需要一段非常短小的SQLSELECT LEVEL, id, parent_id, name FROM category START WITH id 1 CONNECT BY PRIOR id parent_id ORDER SIBLINGS BY name;逐句拆解一下START WITH id 1告诉数据库从哪一行开始往下遍历这一行的LEVEL就是1。如果不写START WITH默认全表每一行都作为根节点结果会重复很多。CONNECT BY PRIOR id parent_id父子关系的连接条件。PRIOR关键字放在哪一边代表谁是父节点。这里PRIOR id表示当前行的id是上一行父行的id也就是说id等于父行id的下一行才是子节点。ORDER SIBLINGS BY按兄弟节点排序同一个父节点下的子节点按name排序。这里不能用普通的ORDER BY否则会把整个树的层次顺序打乱后面会细说。执行后结果大概是LEVEL | ID | PARENT_ID | NAME 1 | 1 | null | 图书 2 | 4 | 1 | 计算机 2 | 2 | 1 | 文学 3 | 3 | 2 | 小说可以看到LEVEL自动从1开始递增每一行的层级一目了然。这就是LEVEL最基础、也最核心的应用。2.2 缩进、路径、根节点LEVEL的三个高级搭档LEVEL最常搭配的几个东西分别是LPAD做缩进、SYS_CONNECT_BY_PATH做路径、CONNECT_BY_ROOT取根节点信息。如果你想把树形数据的层级关系直接体现在展示上可以用空格缩进SELECT LPAD( , (LEVEL - 1) * 2) || name AS tree_name, SYS_CONNECT_BY_PATH(name, /) AS full_path FROM category START WITH parent_id IS NULL CONNECT BY PRIOR id parent_id;这里LPAD根据LEVEL值生成对应数量的空格父节点不缩进第一层子节点缩进两个空格层层递进查询结果看起来就是一棵清晰的目录树。SYS_CONNECT_BY_PATH会把从根到当前行的完整路径拼出来例如“/图书/文学/小说”在生成面包屑导航时非常实用。再说CONNECT_BY_ROOT。它跟LEVEL都是层次查询里的特殊表达式用来获取当前行的根节点字段。比如你要给每一行带上“所属根分类的ID”可以这样写SELECT LEVEL, CONNECT_BY_ROOT id AS root_id, name FROM category START WITH parent_id IS NULL CONNECT BY PRIOR id parent_id;这样的结果中每一行都知道自己属于哪棵树的根节点后续按根节点汇总时尤其有用。我自己的经验是凡是涉及“按顶层分类统计下属所有子类数据”的需求CONNECT_BY_ROOT比任何应用层遍历都高效。2.3 生成连续数字、日期序列LEVEL的另类用法LEVEL除了管树形结构还有一个特别实用的冷门用法用来生成连续序列。原理很简单CONNECT BY条件是11这种恒真条件时数据库会不断往下递归每递归一次LEVEL就加1。生成1到10的连续数字SELECT LEVEL AS n FROM dual CONNECT BY LEVEL 10;生成2024年全年的每一天SELECT DATE 2024-01-01 LEVEL - 1 AS day FROM dual CONNECT BY LEVEL DATE 2024-12-31 - DATE 2024-01-01 1;这个技巧在做报表补全时特别有用。比如订单表里不是每一天都有数据你想按自然日补零传统方法可能要写一堆联合查询用LEVEL先生成日期维度再LEFT JOIN订单数据一下就清爽了。注意生成序列时FROM dual是最安全的。如果写成FROM 某张业务表CONNECT BY LEVEL N结果会变成“这张表的每一行各生成N条记录”数据量直接爆炸。我在第四部分会专门讲这个坑。3. 实战案例组织架构、分类目录、区域汇总三个方向3.1 组织架构与权限树的查询假设有部门表dept结构是dept_id、parent_dept_id、dept_name。你接到的需求是给定一个部门ID查出它下面所有的子部门而且每一条都要知道自己是第几级。老实的做法是在Java里递归查或者用MySQL 8.0以下的版本时临时表拼接。而LEVEL写起来异常直接SELECT LEVEL, dept_id, dept_name FROM dept START WITH dept_id D001 CONNECT BY PRIOR dept_id parent_dept_id;如果想限制只查两级以内加上WHERE LEVEL 2即可。注意这里的WHERE要写在CONNECT BY之后如果写在CONNECT BY之前LEVEL还没生成语法上会有严格限制。反过来如果拿到的不是部门ID而是某个员工ID想查这个员工所属部门的完整上级链路就把父子关系反向连接SELECT LEVEL, dept_id, dept_name FROM dept START WITH dept_id (SELECT dept_id FROM emp WHERE emp_id E001) CONNECT BY PRIOR parent_dept_id dept_id;区别在于CONNECT BY的方向倒过来了。PRIOR parent_dept_id dept_id的意思是当前行的parent_dept_id等于上一行的dept_id也就是从下往上找父节点。平时写树形查询先想清楚“我是从根往下还是从叶子往上”PRIOR放哪边就不会搞反。我在实际项目中还遇到过权限树的场景系统菜单挂在父菜单下不同角色能看到的不同层级范围。比如一个管理员能看到二级菜单普通用户只能看到一级菜单那就在LEVEL上做条件过滤甚至还可以结合CONNECT_BY_ISCYCLE检查权限数据有没有循环引用。整体思路是一样的只是把部门表换成菜单表。3.2 电商分类目录的排序与路径生成电商系统的分类往往是多级树运营会要求同级分类按指定顺序排列。单纯按name或者sort_no排层级就会乱。正确做法是用ORDER SIBLINGS BY。举个例子分类表中有sort_no字段运营手动维护同级排序SELECT LEVEL, LPAD( , (LEVEL - 1) * 2) || name AS tree_name, SYS_CONNECT_BY_PATH(name, /) AS full_path FROM category START WITH parent_id IS NULL CONNECT BY PRIOR id parent_id ORDER SIBLINGS BY sort_no;这样可以确保同一级的分支内部排序稳定而且整棵树的深度顺序不会被破坏。如果你要生成那种“01001001”形式的层级编码LEVEL也能派上用场。思路是先按CONNECT_BY_ID排序取到路径再用SYS_CONNECT_BY_PATH拼出节点的编码路径配合LPAD补零SELECT SYS_CONNECT_BY_PATH(LPAD(sort_no, 3, 0), /) AS level_code, name FROM category START WITH parent_id IS NULL CONNECT BY PRIOR id parent_id ORDER SIBLINGS BY sort_no;结果会生成类似“/001/002/001”的编码串前端拿去做路由跳转、做面包屑、做排序都特别方便。这里有个经验之谈层级编码的生成尽量在SQL层完成不要在应用层拼。因为应用层需要对结果做二次递归才能拿到路径代码量翻倍不说还容易漏掉兄弟排序最终编码顺序和树的展示顺序对不上。3.3 区域销售数据的逐级汇总业务报表里经常有一种需求按区域汇总销售额区域下面是省份省份下面是城市要一眼看出华北区、华东区各自的合计而且还要能下钻。LEVEL不能直接做聚合但它和CONNECT_BY_ROOT搭配可以很轻松地把每一行数据归到根节点下做汇总。假设有区域表regionregion_id、parent_region_id、region_name销售表salesregion_id、amount。现在想按顶层区域汇总SQL可以这样写SELECT root_region, SUM(amount) AS total_amount FROM ( SELECT CONNECT_BY_ROOT(r.region_id) AS root_region, s.amount FROM region r LEFT JOIN sales s ON s.region_id r.region_id START WITH r.parent_region_id IS NULL CONNECT BY PRIOR r.region_id r.parent_region_id ) GROUP BY root_region;这里子查询先把整棵区域树查出来同时用CONNECT_BY_ROOT给每个区域行打上“顶层根区域”的标记然后外层只需要按root_region分组求和即可。如果要看省级汇总就在内层把聚合维度换成region_id或者在外层再按根节点和当前节点同时分组。我第一次做这种报表时用了三层Java循环逐层查数据库最后汇总。后来改成一条SQL接口从800毫秒降到80毫秒而且逻辑非常透明任何一个后来接手的人都能看懂。这就是LEVEL在真实工程里的价值把原本分布在应用层和SQL层的复杂工作收敛到数据库一次完成。4. 你大概率会踩的坑排序、死循环、CONNECT BY LEVEL的副作用4.1 排序别用ORDER BY要用ORDER SIBLINGS BY这是我早期踩过最深的一个坑。树形查询的天然输出顺序是“先父后子、深度优先”但如果你用ORDER BY去排某个字段数据库会把整棵树的顺序完全打乱。具体表现是某个父节点下面的子节点可能跑到其他父节点后面第三层节点排到了第一层前面。整个树的层级感没了。正确做法是ORDER SIBLINGS BY注意它和ORDER BY不是一回事ORDER BY对最终结果集做全量排序会破坏树形结构。ORDER SIBLINGS BY只针对同一父节点下的同级节点排序不影响父子层级顺序。写的时候还要注意ORDER SIBLINGS BY的位置在CONNECT BY之后而且只能出现在层次查询里。如果你在普通查询里误用了它SQL直接报错。4.2 死循环CONNECT BY loop in user data树形数据最头疼的问题就是数据变成环。比如dept表里A部门的parent_dept_id指向B部门B部门的parent_dept_id又指回A部门数据库在递归时就陷入无限循环最终报错ORA-01436CONNECT BY loop in user data。碰到这种问题第一步是定位脏数据。写个SQL自查一下父子关系成环的记录这一步比什么技巧都重要。第二步是给查询加NOCYCLESELECT LEVEL, dept_id, dept_name FROM dept START WITH dept_id D001 CONNECT BY NOCYCLE PRIOR dept_id parent_dept_id;NOCYCLE的意思是发现循环时跳过不报错继续处理其他节点。同时还可以用CONNECT_BY_ISCYCLE伪列标记哪些行触发了环方便你回头排查数据。注意NOCYCLE只是让查询不报错不等于数据是对的。生产环境一旦遇到ORA-01436必须先修数据再考虑查询兜底。我的习惯是给树形表的写入过程加一个约束校验插入节点时检查parent链路上是否已经出现过当前节点从源头杜绝成环。4.3 CONNECT BY LEVEL的隐藏副作用很多人第一次写“CONNECT BY LEVEL 10”时顺手把它接在业务表后面SELECT LEVEL, name FROM category CONNECT BY LEVEL 10;表面上看你以为会生成10行结果但实际上如果category表里有100行数据结果会变成100的10次方级别的笛卡尔积查询直接卡死。原因很简单CONNECT BY会把FROM里的每一行分别作为递归起点每一行都向下展开10层。LEVEL只是标记深度它不是行号也不是结果集的上限。所以生成连续序列一定要FROM dual这种单行表或者FROM (SELECT 1 FROM dual)这种固定一行的子查询。如果确实想基于业务表做一定行数的展开必须先想清楚递归起点是什么、每一行展开的层级是什么别让数据库白白产生海量中间结果。我在一个数据初始化脚本里就吃过这个亏。当时表里只有几百行测试数据我写了一条“CONNECT BY LEVEL 5”的补数SQL结果跑了二十分钟没出来DBA直接发消息问我在搞什么。后来改成从dual出发再JOIN业务表秒出结果。4.4 不同数据库的层次查询差异伪列LEVEL和CONNECT BY语法在主流商业数据库和通用数据库里并不完全兼容。如果你切换到开源的团队或项目最常遇到的是下面这种对比场景Oracle系层次查询语法MySQL 8.0/通用递归CTE查整棵子树START WITH ... CONNECT BY PRIORWITH RECURSIVE ... UNION ALL获取层级深度LEVEL自定义level字段每次递归加1拼接路径SYS_CONNECT_BY_PATH自定义path字段CONCAT拼接兄弟节点排序ORDER SIBLINGS BY递归结束后按path排序MySQL 8.0引入通用表表达式WITH RECURSIVE之前写树形查询确实痛苦大多数是应用层拼。有了递归CTE后同样也能拿到层级深度只是没有LEVEL这么方便的伪列需要自己维护一个递归深度字段。我给你一个简单的MySQL递归CTE写法也是查分类树WITH RECURSIVE category_tree AS ( SELECT id, parent_id, name, 1 AS level FROM category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, ct.level 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT * FROM category_tree;这种情况下LEVEL就是你自己定义的别名核心思路和CONNECT BY如出一辙。迁移SQL时只要掌握“递归起点”对应START WITH和“递归连接条件”对应CONNECT BY PRIOR两个关键点改写并不难。我建议团队里如果两个数据库都在用就统一维护一份递归CTE版本兼容性更好理解成本也更低。5. 伪列查询之外选对层次查询方案的一些经验5.1 CONNECT BY 和递归CTE怎么选写层次查询时经常有人纠结用CONNECT BY还是WITH RECURSIVE我的判断标准很简单分三条数据库生态如果你的底座是Oracle系CONNECT BY是原生语法LEVEL现成写得最快如果你用的是MySQL或SQL Server优先用递归CTE别惦记CONNECT BY。可读性递归CTE的结构更清晰它把“起点”和“递归规则”分成两段新同学上手容易CONNECT BY写起来短但对不熟悉的人容易看晕。功能覆盖CONNECT BY家族里有很多专用函数和伪列比如SYS_CONNECT_BY_PATH、CONNECT_BY_ROOT这些在递归CTE里没有现成等价物实现同样效果要手动拼接。如果是老项目改造我建议不要硬改语法。比如Oracle系的系统里LEVEL已经用得滚瓜烂熟你非把它改成递归CTE收益不大还有回归风险。反过来新项目如果用MySQL老老实实用WITH RECURSIVE就好。5.2 伪列、虚拟列、生成列不是一回事跟LEVEL打交道多了很容易把“伪列”和“虚拟列”“生成列”搞混。这里顺手理清伪列LEVEL、ROWNUM、ROWID等查询时由数据库动态计算出来的逻辑列不在表结构中存储不能像普通字段那样被INSERT或UPDATE。虚拟列/生成列建表时定义的表达式列比如MySQL的GENERATED ALWAYS AS (expr)列值由其他字段计算而来可以存储也可以建索引。普通物化列最传统的字段值由业务写入。这三者的使用边界完全不同。LEVEL这类伪列解决的是“查询过程中如何表达关系与层级”的问题虚拟列解决的是“表里的冗余字段如何自动维护”的问题。比如你想让分类表在写入时自动维护一个层级编码列可以在表上建生成列但这跟查询时的LEVEL没有互相替代关系是两条独立的技术线。5.3 树表结构设计对LEVEL查询的影响最后说一个经常被忽视的点树形表怎么设计直接决定LEVEL查询好不好写。最常用的是邻接表模型即每一行只存parent_id这也是CONNECT BY最适配的模型。优点是写入简单、子查询灵活缺点是深层级查询要走递归数据量大时对索引要求高。我通常会这样设计parent_id列一定加索引否则递归时每一层的连接条件都是全表扫性能惨不忍睹。如果经常要按根节点汇总考虑冗余一个root_id字段应用层维护或者用触发器维护比每次查询都CONNECT_BY_ROOT快很多。如果树的深度固定而且查询深度不会太深也可以考虑路径枚举模型直接用一个层级路径字符串表示节点位置查询时LIKE匹配即可连CONNECT BY都省了。但要注意路径枚举模型虽然读快写起来麻烦插入中间节点或调整层级路径字段全部要批量更新。所以大原则是读多写少用路径枚举或闭包表写多读少用邻接表配LEVEL。很多系统之所以树形接口慢不是SQL写得烂是表结构一开始就没给查询留余地。我个人在实际操作中的体会是凡是树形数据接口先花五分钟把递归起点、方向、排序方式三件事想清楚再动笔写SQL基本一次成型。当年我处理地区树查询就是没想清楚“从根往下查子部门”和“从员工往上查链路”的区别PRIOR方向写反结果查出来的数据完全是颠倒的排查了一个下午。后来再看“伪列level的应用”其实核心就是一件事让数据库帮你按树的结构去遍历你只需要描述清楚从哪开始、父子怎么连、层级要显现出来剩下的交给LEVEL就行。