系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点

文章目录

  • 系列文章目录
  • 前言
    • 一、简单的树形查询案例
    • 二、根节点、分支节点、叶子节点的确定
    • 三、查看节点路径函数 sys_connect_by_path
  • 总结

前言

本篇文章讲解的主要内容是：第一个案例给大家介绍如何在Oracle中进行树形(分层)查询，主要介绍START WITH、CONNECT BY、PRIOR语法的使用。接着以如何确定根节点、分支节点和叶子节点的需求，给大家介绍level与connect_by_isleaf函数的使用。最后介绍节点路径函数 sys_connect_by_path以及它在Oracle11.2之前可以替代Listagg函数和wm_concat函数的写法。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、简单的树形查询案例

我们经常会用一些表来保存上下级关系,比如总公司表、分公司表、员工表等,为了按上下级关系递归查询这些数据,就需要用到树形查询,下面以emp表为例。
先来看下emp表的数据：

SQL> select * from emp;EMPNO ENAMEJOB MGR HIREDATESALCOMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITHCLERK7902 1980-12-17 800.00 20 7499 ALLENSALESMAN 7698 1981-2-20 1600.00300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00500.00 30 7566 JONESMANAGER7839 1981-4-22975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKEMANAGER7839 1981-5-12850.00 30 7782 CLARKMANAGER7839 1981-6-92450.00 10 7788 SCOTTANALYST7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-175000.00 10 7844 TURNER SALESMAN 7698 1981-9-81500.000.00 30 7876 ADAMSCLERK7788 1987-5-23 1100.00 20 7900 JAMESCLERK7698 1981-12-3950.00 30 7902 FORD ANALYST7566 1981-12-3 3000.00 20 7934 MILLER CLERK7782 1982-1-23 1300.00 1014 rows selectedSQL>

我们来以empno=7566 为起点，向下递归查询员工和主管关系：

SELECT empno AS 员工编码,ename AS 姓名,mgr AS 主管编码,(PRIOR ename) AS 主管姓名FROM empSTART WITH empno=7566CONNECT BY (PRIOR empno)=mgr; 员工编码 姓名主管编码 主管姓名----- ---------- ----- ---------- 7566 JONES 78397788 SCOTT 7566 JONES 7876 ADAMS 7788 SCOTT 7902 FORD7566 JONES 7369 SMITH 7902 FORD

来分析一下上面语句。

  • 起点：这个语句以"START WITH empno=7566"为起点向下递归查询。

通过操作符”PRIOR”可以取得上一级的信息,如上面查询中的主管姓名(PRIOR ename)。
CONNECT BY子句列出来递归的条件（上一级的编码）等于本级的主管编码。然后再以7566为起点,向下一级就是(7788,7902)。
7788的下一级是7876,7902的下一级是7369。至此,递归查找完成。

二、根节点、分支节点、叶子节点的确定

在树形查询中常用的有两个伪列：level与connect_by_isleaf。level返回当前行所在的等级,根节点为1级,其下为2级……
如果当前节点下没有其他的节点,则connect_by_isleaf 返回1,否则返回0。这样就可以通过level与connect_by_isleaf来判断标识根节点、分支节点、叶子节点。如下案例：

SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码, ename AS 姓名, mgr AS 主管编码, (PRIOR ename) AS 主管姓名, level as 级别, decode(level, 1, 1) as 根节点, decode(connect_by_isleaf, 1, 1) as 叶子节点, CASE WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN1 END AS 分支节点FROM emp START WITH empno = 7566CONNECT BY (PRIOR empno) = mgr;员工编码 姓名主管编码 主管姓名 级别根节点 叶子节点 分支节点-------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ----------7566 JONES 7839 11->7788 SCOTT 7566 JONES 21->->7876 ADAMS 7788 SCOTT 3 1 ->7902 FORD7566 JONES 21->->7369 SMITH 7902 FORD3 1

三、查看节点路径函数 sys_connect_by_path

当数据级别比较多时,不容易看清根节点到当前节点的路径,这时就可用sys_connect_by_path函数把这些信息展示出来：

SELECT lpad('->', (level - 1) * 2, '->') || empno AS 员工编码, ename AS 姓名, mgr AS 主管编码, (PRIOR ename) AS 主管姓名, level as 级别, decode(level, 1, 1) as 根节点, decode(connect_by_isleaf, 1, 1) as 叶子节点, CASE WHEN (connect_by_isleaf = 0 AND LEVEL > 1) THEN1 END AS 分支节点, sys_connect_by_path(ename,'->') as enamesFROM emp START WITH empno = 7566CONNECT BY (PRIOR empno) = mgr;员工编码 姓名主管编码 主管姓名 级别根节点 叶子节点 分支节点 ENAMES-------------------------------------------------------------------------------- ---------- ----- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------7566 JONES 7839 11 ->JONES->7788 SCOTT 7566 JONES 21 ->JONES->SCOTT->->7876 ADAMS 7788 SCOTT 3 1->JONES->SCOTT->ADAMS->7902 FORD7566 JONES 21 ->JONES->FORD->->7369 SMITH 7902 FORD3 1->JONES->FORD->SMITH

前面介绍过用分析函数listagg来合并字符串,然而Oracle11.2之前的版本没有listagg怎么办？其实可以借助树形查询中的sys_connect_by_path函数：

with t as/*分组生成序号*/ (select deptno, ename, row_number() over(partition by deptno order by ename) as rnfrom emp)/*用sys_connect_by_path合并字符串*/select deptno, sys_connect_by_path(ename, ',') as empsfrom t where connect_by_isleaf = 1 --要取出来叶子节点所在的那个分支才是完整的拼接 start with rn = 1connect by (prior deptno) = deptno and (prior rn) = rn - 1;DEPTNO EMPS------ --------------------------------------------------------------------------------10 ,CLARK,KING,MILLER20 ,ADAMS,FORD,JONES,SCOTT,SMITH30 ,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

这种方法的要点是分组生成序号,然后通过序号递归循环。注意：要过滤多余的数据时,只需要加条件”WHEREconnect_by_isleaf=1“来取叶子节点就可以。

总结

本篇文章讲解的主要内容是：第一个案例给大家介绍如何在Oracle中进行树形(分层)查询，主要介绍START WITH、CONNECT BY、PRIOR语法的使用。接着以如何确定根节点、分支节点和叶子节点的需求，给大家介绍level与connect_by_isleaf函数的使用。最后介绍节点路径函数 sys_connect_by_path以及它在Oracle11.2之前可以替代Listagg函数和wm_concat函数的写法。