数据库学习笔记---SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)
发布日期:2021-06-29 17:56:08
浏览次数:2
分类:技术文章
本文共 5589 字,大约阅读时间需要 18 分钟。
SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR) 技术qq交流群:JavaDream:251572072 教程下载,在线交流:创梦IT社区:____________ 辣鸡了...以前用的域名现在不用了 --============================================================================================================ 层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下: SELECT [LEVEL] ,column,expression,... FROM table_name [WHERE where_clause] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]]; LEVEL:为伪列,用于表示树的层次 start_condition:层次化查询的起始条件 prior_condition:定义父节点和子节点之间的关系 --使用start with ...connect by prior 从根节点开始遍历 SQL> select empno,mgr,ename,job from emp 2 start with empno = 7839 3 connect by prior empno = mgr; EMPNO MGR ENAME JOB ---------- ---------- ---------- --------- 7839 KING PRESIDENT 7566 7839 JONES MANAGER 7788 7566 SCOTT ANALYST 7876 7788 ADAMS CLERK 7902 7566 FORD ANALYST 7369 7902 SMITH CLERK 7698 7839 BLAKE MANAGER 7499 7698 ALLEN SALESMAN 7521 7698 WARD SALESMAN 7654 7698 MARTIN SALESMAN 7844 7698 TURNER SALESMAN EMPNO MGR ENAME JOB ---------- ---------- ---------- --------- 7900 7698 JAMES CLERK 7782 7839 CLARK MANAGER 7934 7782 MILLER CLERK 14 rows selected. 树型结构遍历过程(通过上面的查询来描述) 1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839) 2).遍历根节点(得到empno = 7839记录的相关信息) 3).判断该节点是否存在由子节点,如果则访问最左侧未被访问的子节点,转到),否则下一步 如上例中prior_condition为empno = mgr,即子节点的mgr等于父节点的empno,在此时mgr为7839的记录 4).当节点为叶节点,则访问完毕,否则,转到) 5).返回到该节点的父节点,转到) --伪列level的使用 --注意connect by prior empno = mgr 的理解 --prior表示前一条记录,即下一条返回记录的mgr应当等于前一条记录的empno SQL> select level,empno,mgr,ename,job from emp 2 start with ename = 'KING' 3 connect by prior empno = mgr 4 order by level; LEVEL EMPNO MGR ENAME JOB ---------- ---------- ---------- ---------- --------- 1 7839 KING PRESIDENT 2 7566 7839 JONES MANAGER 2 7698 7839 BLAKE MANAGER 2 7782 7839 CLARK MANAGER 3 7902 7566 FORD ANALYST 3 7521 7698 WARD SALESMAN 3 7900 7698 JAMES CLERK 3 7934 7782 MILLER CLERK 3 7499 7698 ALLEN SALESMAN 3 7788 7566 SCOTT ANALYST 3 7654 7698 MARTIN SALESMAN LEVEL EMPNO MGR ENAME JOB ---------- ---------- ---------- ---------- --------- 3 7844 7698 TURNER SALESMAN 4 7876 7788 ADAMS CLERK 4 7369 7902 SMITH CLERK --获得层次数 SQL> select count(distinct level) "Level" from emp 2 start with ename = 'KING' 3 connect by prior empno = mgr; Level ---------- 4 --格式化层次查询结果(使用左填充* level - 1个空格) SQL> col Ename for a30 SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename", 3 job 4 from emp 5 start with ename = 'KING' 6 connect by prior empno = mgr; LEVEL Ename JOB ---------- ------------------------------ --------- 1 KING PRESIDENT 2 JONES MANAGER 3 SCOTT ANALYST 4 ADAMS CLERK 3 FORD ANALYST 4 SMITH CLERK 2 BLAKE MANAGER 3 ALLEN SALESMAN 3 WARD SALESMAN 3 MARTIN SALESMAN 3 TURNER SALESMAN LEVEL Ename JOB ---------- ------------------------------ --------- 3 JAMES CLERK 2 CLARK MANAGER 3 MILLER CLERK 14 rows selected. --从非根节点开始遍历(只需修改start with 中的条件即可) SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename", 3 job 4 from emp 5 start with ename = 'SCOTT' 6 connect by prior empno = mgr; LEVEL Ename JOB ---------- ------------------------------ --------- 1 SCOTT ANALYST 2 ADAMS CLERK --从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno) --注意connect by prior mgr = empno 的理 --prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename", 3 job 4 from emp 5 start with ename = 'SCOTT' 6 connect by prior mgr = empno; LEVEL Ename JOB ---------- ------------------------------ --------- 1 SCOTT ANALYST 2 JONES MANAGER 3 KING PRESIDENT --从下向上遍历(也可以将prior置于等号右边,得到相同的结果) SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename", 3 job 4 from emp 5 start with ename = 'SCOTT' 6 connect by empno = prior mgr; LEVEL Ename JOB ---------- ------------------------------ --------- 1 SCOTT ANALYST 2 JONES MANAGER 3 KING PRESIDENT --从层次查询中删除节点和分支 SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename" 3 ,job 4 from emp 5 where ename != 'SCOTT' --通过where子句来过滤SCOTT用户,但SCOTT的下属ADAMS并没有过滤掉 6 start with empno = 7839 7 connect by prior empno = mgr; LEVEL Ename JOB ---------- -------------------- --------- 1 KING PRESIDENT 2 JONES MANAGER 4 ADAMS CLERK 3 FORD ANALYST 4 SMITH CLERK 2 BLAKE MANAGER 3 ALLEN SALESMAN 3 WARD SALESMAN 3 MARTIN SALESMAN 3 TURNER SALESMAN 3 JAMES CLERK LEVEL Ename JOB ---------- -------------------- --------- 2 CLARK MANAGER 3 MILLER CLERK 13 rows selected. --通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename" 3 ,job 4 from emp 5 start with empno = 7839 6 connect by prior empno = mgr and ename != 'SCOTT'; LEVEL Ename JOB ---------- -------------------- --------- 1 KING PRESIDENT 2 JONES MANAGER 3 FORD ANALYS 4 SMITH CLERK 2 BLAKE MANAGER 3 ALLEN SALESMAN 3 WARD SALESMA 3 MARTIN SALESMAN 3 TURNER SALESMAN 3 JAMES CLERK 2 CLARK MANAGER LEVEL Ename JOB ---------- -------------------- --------- 3 MILLER CLERK 12 rows selected. --在层次化查询中增加过滤条件或使用子查询 SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename" 3 ,job 4 from emp 5 where sal > 2500 6 start with empno = 7839 7 connect by prior empno = mgr 8 ; LEVEL Ename JOB ---------- -------------------- --------- 1 KING PRESIDENT 2 JONES MANAGER 3 SCOTT ANALYST 3 FORD ANALYST 2 BLAKE MANAGER SQL> select level, 2 lpad(' ',2 * level - 1) || ename as "Ename" 3 ,job 4 from emp 5 where sal > (select avg(sal) from emp) 6 start with empno = 7839 7 connect by prior empno = mgr ; LEVEL Ename JOB ---------- -------------------- --------- 1 KING PRESIDENT 2 JONES MANAGER 3 SCOTT ANALYST 3 FORD ANALYST 2 BLAKE MANAGER 2 CLARK MANAGER 6 rows selected.转载地址:https://credream.blog.csdn.net/article/details/15473865 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
哈哈,博客排版真的漂亮呢~
[***.90.31.176]2024年04月08日 18时36分07秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
树莓派网线直连
2019-04-29
复合材料培训(I第七期)
2019-04-29
复合材料生活中的应用
2019-04-29
ABAQUS复合材料(适合小白)
2019-04-29
ABAQUS高级案例解析
2019-04-29
人工智能药物研发
2019-04-29
【超级干货+福利】AIDD最全面的学习教程
2019-04-29
最新通知:AIDD与网络药理学资料大全
2019-04-29
Lammps分子动力学与第一性原理材料模拟及催化
2019-04-29
实习生小白的日常
2019-04-29
实习小白的日常(4)
2019-04-29
微信扫码登录验证PHP代码(不用开放平台)
2019-04-29
CH554E USB单片机 10引脚小封装低成本USB方案
2019-04-29
windows MQTT客户端
2019-04-29
LINUX下挂载(mount)查看树莓派镜像文件
2019-04-29
1元钱的超低成本单芯片USB单片机方案
2019-04-29
单片机/树莓派扩展双串口(TTL和RS485)
2019-04-29
基于CH568芯片的SATA电子盘方案
2019-04-29
linux下C语言判断网络是否连接
2019-04-29