oracle 树查询 语句
来源:asp之家 发布时间:2009-07-17 18:20:00
格式:
SELECT column
FROM table_name
START WITH column=value
CONNECT BY PRIOR 父主键=子外键
select lpad(' ',4*(level-1))||name name,job,id,super from emp
start with super is null
connect by prior id=super
例子:
原始数据:select no,q from a_example2
NO NAME
---------- ------------------------------
001 a01
001 a02
001 a03
001 a04
001 a05
002 b01
003 c01
003 c02
004 d01
005 e01
005 e02
005 e03
005 e04
005 e05
需要实现得到结果是:
001 a01;a02;a03
002 b01
003 c01;c02
004 d01
005 e01;e02;e03;e04;e05
思路:
1、ORACLE8.1之后有个connect by 子句,取出整棵树数据。
create table a_example1
(
no char(3) not null,
name varchar2(10) not null,
parent char(3)
)
insert into a_example1
values('001','老王',null)
insert into a_example1
values('101','老李',null)
insert into a_example1
values('002','大王1','001')
insert into a_example1
values('102','大李1','101')
insert into a_example1
values('003','大王2','001')
insert into a_example1
values('103','大李2','101')
insert into a_example1
values('003','小王1','002')
insert into a_example1
values('103','小李1','102')
NO NAME PARENT
001 老王
101 老李
002 大王1 001
102 大李1 101
003 大王2 001
103 大李2 101
003 小王1 002
103 小李1 102
//按照家族树取数据
select * from a_example1
select level,sys_connect_by_path(name,'/') path
from a_example1
start with /*name = '老王' and*/ parent is null
connect by parent = prior no
结果:
1 /老王
2 /老王/大王1
3 /老王/大王1/小王1
2 /老王/大王2
1 /老李
2 /老李/大李1
3 /老李/大李1/小李1
2 /老李/大李2
按照上面思路,我们只要将原始数据做成如下结构:
NO NAME
001 a01
001 a01/a02
001 a01/a02/a03
001 a01/a02/a03/a04
001 a01/a02/a03/a04/a05
002 b01
003 c01
003 c01/c02
004 d01
005 e01
005 e01/e02
005 e01/e02/e03
005 e01/e02/e03/e04
005 e01/e02/e03/e04/e05
最后按NO分组,取最大的一个值即为所需的结果。
NO NAME
001 a01/a02/a03/a04/a05
002 b01
003 c01/c02
004 d01
005 e01/e02/e03/e04/e05
SQL语句:
select no,max(sys_connect_by_path(name,';')) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
语句分析:
1、 select no,name,row_number() over(order by no,name desc) rn from a_example2
按照NO升序排序,同时按照NAME降序排序,产生伪列,目的是要形成树结构
NO NAME RN
001 a03 1
001 a02 2
001 a01 3
002 b01 4
003 c02 5
003 c01 6
004 d01 7
005 e05 8
005 e04 9
005 e03 10
005 e02 11
005 e01 12
2、select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from ( select no,name,row_number() over(order by no,name desc) rn from a_example2)
生成家族谱,即子节点与父节点有对应关系,对应关系通过rn和 rn1。其中lead为上一条记录的RN值
NO NAME RN RN1 001 a03 1 2 --
说明:针对NO=001来说,其下一条记录的RN=2 001 a02 2 3 --说明:针对NO=001来说,其下一条记录的RN=3 001 a01 3 --说明:针对NO=001来说,其下一条记录的RN IS NULL
002 b01 4 003 c02 5 6 003 c01 6 004 d01 7 005 e05 8 9 005 e04 9 10 005 e03 10 11 005 e02 11 12 005 e01 12
3、select no,sys_connect_by_path(name,';') result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from ( select no,name,row_number() over(order by no,name desc) rn from a_example2))
start with rn1 is null connect by rn1 = prior rn
正式生成树
NO RESULT
001 ;a01
001 ;a01;a02
001 ;a01;a02;a03
002 ;b01
005 ;e01
005 ;e01;e02
005 ;e01;e02;e03
005 ;e01;e02;e03;e04
005 ;e01;e02;e03;e04;e05
003 ;c01
003 ;c01;c02
004 ;d01
将上面结果按照NO分组,取result最大值即可,所以将上述语句改为
select no,max(sys_connect_by_path(name,';')) result from
(select no,name,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,name,row_number() over(order by no,name desc) rn from a_example2)
)
start with rn1 is null connect by rn1 = prior rn
group by no
得到所需结果。


猜你喜欢
- MySQL 客户端连接成功后,通过 show [session|global]status 命令 可以提供服务器状态信息,也可以在操作系统上
- 本文的文字及图片来源于网络,仅供学习、交流使用,不具有任何商业用途,如有问题请及时联系我们以作处理。以下文章来源于数据STUDIO,作者龙哥
- 一、控制用户存取 1、创建修改用户Creating Users Create/alter user new_user identified
- 本文实例讲述了js设计模式之单例模式原理与用。分享给大家供大家参考,具体如下:关于设计模式,我的理解是它是业务代码的提前解决方案。意思就是说
- 输出带颜色的字符串,用来显示要突出的部分。经测验,在pycharm中可行,在windows命令行中不可行。原因未知。方法:格式:"
- 一.图像加法运算1.Numpy库加法其运算方法是:目标图像 = 图像1 + 图像2,运算结果进行取模运算。当像素值<=255时,结果为
- 1. 介绍-- PHP5添加了一项新的功能:Reflection。这个功能使得phper可以reverse-engineer class,
- composer是PHP中非常优秀的一个包管理工具,可以帮助我们来管理项目所需要依赖的所有包以及插件,也可以用来从packagist中创建一
- 下面这段代码能够显示,当前用户所能够看到的所有的用户和表有兴趣的, 可以把每个表的内容加上<% Dim objOraSess
- User Centered Design 以用户为中心的设计,一说到这个很多人马上想到互联网和软件的设计,因为在IT、互联网行业提得最多。其
- 本文实例讲述了PHP缓存集成库phpFastCache用法。分享给大家供大家参考。具体分析如下:phpFastCache是一个开源的PHP缓
- #coding:utf-8from wsgiref.simple_server import make_serverdef RunServe
- 第一步:获取mysql YUM源进入mysql官网获取RPM包下载地址https://dev.mysql.com/downloads/rep
- 卸载MariaDBCentOS7默认安装MariaDB而不是MySQL,而且yum服务器上也移除了MySQL相关的软件包。因为MariaDB
- 如何显示数据库里的图片?asp调用数据库中的图片并显示。怎样把数据库里的图片显示出来?我们以gif格式的图片为例,代码如下:showimag
- 介绍提到爬虫,互联网的朋友应该都不陌生,现在使用Python爬取网站数据是非常常见的手段,好多朋友都是爬取豆瓣信息为案例,我不想重复,就使用
- js写常用导航鼠标下经过下方横线自动跟随html代码如下:<div class="header"> &nbs
- 前言在对着产品高举中指怒发心中之愤后,真正能够解决问题的是自身上的改变,有句话说的好:你虽然改变不了全世界,但是你有机会改变你自己。秉承着“
- 技术栈win7+python3+selenium之前有个需求需要实现自动化提流程,为了在任何电脑都能实现该功能,特此写了个爬虫,并将其打包成
- HP注释规范注释在写代码的过程中非常重要,好的注释能让你的代码读起来更轻松,在写代码的时候一定要注意注释的规范。“php是一门及其容易入门的