Sql递归查询
时间：2021-07-01 10:21:17
帮助过：5人阅读
Sql递归查询*/
/*
实际就是把所有树的节点查找出来
Oracle的一个表中也可以保存树形结构信息，用start with...connect by等关键字
eg:创建表并插入数据
*/
Create table Tree(son
char(
10),father
char(
10));
insert into tree (SON, FATHER)
values (
‘孙子1‘,
‘儿子‘);
insert into tree (SON, FATHER)
values (
‘孙子2‘,
‘儿子‘);
insert into tree (SON, FATHER)
values (
‘儿子‘,
‘父亲‘);
insert into tree (SON, FATHER)
values (
‘父亲‘,
‘祖父‘);
/*
son father
孙子1 儿子
孙子2 儿子
儿子 父亲
父亲 祖父
数据结构是:
祖父
|
父亲
|
儿子
/ 孙子1 孙子2
*/
--查询以祖父为根节点的所有节点值
SELECT son
from Tree START
WITH father
=‘祖父‘ CONNECT
BY PRIOR son
=father
/*
START WITH后的内容可以看成一个WHERE的限制条件，其中START WITH 是指定树的根
，还可以指定多个根，比如father in (‘祖父‘,‘父亲‘);
CONNECTION BY PRIOR son=father相当于表明在递归过程中，查找到的树种其他节点接着
又作为根结点，然后继续递归。
只要记住 CONNECTION BY PRIOR那一行，要查询的列名放在前，根列名放等号后
*/
--例：
--树形菜单结构
CREATE TABLE tb_menu(
id number(
10)
not null,
--主键id
title
varchar(
50),
--标题
parent
number(
10)
--父菜单id
);
--父菜单数据
insert into tb_menu(id,title,parent)
values(
1,
‘父菜单1‘,
0);
insert into tb_menu(id,title,parent)
values(
2,
‘父菜单2‘,
0);
insert into tb_menu(id,title,parent)
values(
3,
‘父菜单3‘,
0);
insert into tb_menu(id,title,parent)
values(
4,
‘父菜单4‘,
0);
insert into tb_menu(id,title,parent)
values(
5,
‘父菜单5‘,
0);
--一级子菜单
insert into tb_menu(id,title,parent)
values(
6,
‘一级子菜单6_1‘,
1);
insert into tb_menu(id,title,parent)
values(
7,
‘一级子菜单7_1‘,
1);
insert into tb_menu(id,title,parent)
values(
8,
‘一级子菜单8_1‘,
1);
insert into tb_menu(id,title,parent)
values(
9,
‘一级子菜单9_2‘,
2);
insert into tb_menu(id,title,parent)
values(
10,
‘一级子菜单10_2‘,
2);
insert into tb_menu(id,title,parent)
values(
11,
‘一级子菜单11_2‘,
2);
insert into tb_menu(id,title,parent)
values(
12,
‘一级子菜单12_3‘,
3);
insert into tb_menu(id,title,parent)
values(
13,
‘一级子菜单13_3‘,
3);
insert into tb_menu(id,title,parent)
values(
14,
‘一级子菜单14_3‘,
3);
insert into tb_menu(id,title,parent)
values(
15,
‘一级子菜单15_4‘,
4);
insert into tb_menu(id,title,parent)
values(
16,
‘一级子菜单16_4‘,
4);
insert into tb_menu(id,title,parent)
values(
17,
‘一级子菜单17_4‘,
4);
insert into tb_menu(id,title,parent)
values(
18,
‘一级子菜单18_5‘,
5);
insert into tb_menu(id,title,parent)
values(
19,
‘一级子菜单19_5‘,
5);
insert into tb_menu(id,title,parent)
values(
20,
‘一级子菜单20_5‘,
5);
--二级子菜单
insert into tb_menu(id,title,parent)
values(
21,
‘二级子菜单21_6‘,
6);
insert into tb_menu(id,title,parent)
values(
22,
‘二级子菜单22_6‘,
6);
insert into tb_menu(id,title,parent)
values(
23,
‘二级子菜单23_7‘,
7);
insert into tb_menu(id,title,parent)
values(
24,
‘二级子菜单24_7‘,
7);
insert into tb_menu(id,title,parent)
values(
25,
‘二级子菜单25_8‘,
8);
insert into tb_menu(id,title,parent)
values(
26,
‘二级子菜单26_9‘,
9);
insert into tb_menu(id,title,parent)
values(
27,
‘二级子菜单27_10‘,
10);
insert into tb_menu(id,title,parent)
values(
28,
‘二级子菜单28_11‘,
11);
insert into tb_menu(id,title,parent)
values(
29,
‘二级子菜单29_12‘,
12);
insert into tb_menu(id,title,parent)
values(
30,
‘二级子菜单30_13‘,
13);
insert into tb_menu(id,title,parent)
values(
31,
‘二级子菜单31_14‘,
14);
insert into tb_menu(id,title,parent)
values(
32,
‘二级子菜单32_15‘,
15);
insert into tb_menu(id,title,parent)
values(
33,
‘二级子菜单33_16‘,
16);
insert into tb_menu(id,title,parent)
values(
34,
‘二级子菜单34_17‘,
17);
insert into tb_menu(id,title,parent)
values(
35,
‘二级子菜单35_18‘,
18);
insert into tb_menu(id,title,parent)
values(
36,
‘二级子菜单36_19‘,
19);
insert into tb_menu(id,title,parent)
values(
37,
‘二级子菜单37_20‘,
20);
--三级子菜单
insert into tb_menu(id,title,parent)
values(
38,
‘三级子菜单38_21‘,
21);
insert into tb_menu(id,title,parent)
values(
39,
‘三级子菜单39_22‘,
22);
insert into tb_menu(id,title,parent)
values(
40,
‘三级子菜单40_23‘,
23);
insert into tb_menu(id,title,parent)
values(
41,
‘三级子菜单41_24‘,
24);
insert into tb_menu(id,title,parent)
values(
42,
‘三级子菜单42_25‘,
25);
insert into tb_menu(id,title,parent)
values(
43,
‘三级子菜单43_26‘,
26);
insert into tb_menu(id,title,parent)
values(
44,
‘三级子菜单44_27‘,
27);
insert into tb_menu(id,title,parent)
values(
45,
‘三级子菜单45_28‘,
28);
insert into tb_menu(id,title,parent)
values(
46,
‘三级子菜单46_28‘,
28);
insert into tb_menu(id,title,parent)
values(
47,
‘三级子菜单47_29‘,
29);
insert into tb_menu(id,title,parent)
values(
48,
‘三级子菜单48_30‘,
30);
insert into tb_menu(id,title,parent)
values(
49,
‘三级子菜单49_31‘,
31);
insert into tb_menu(id,title,parent)
values(
50,
‘三级子菜单50_31‘,
31);
/*
数据结构：
父菜单1
|
/ 一级子菜单1_6 ... ...
/
二级子菜单21_6... ...
/
三级子菜单38_21... ... ...
*/
--①查询表中所有父菜单
select * from tb_menu m
where m.parent
=0;
--②查询表中所有一级子菜单(即数据结构中的直属子节点)
select * from tb_menu m
where m.parent
=1;
--③查询父菜单1的所有子孙
select * from tb_menu m start
with m.id
=1 connect
by PRIOR m.id
=m.parent
/*
这个SQL语句的等价写法：
select * from tb_menu m start with m.id=1 connect by m.parent=PRIOR m.id
可以记做：
prior放在那里，就找谁，即放在id前，即为找id的所有子孙后代
*/
--④查询一个节点的直属父节点(用不到树形查询)
select
c.id,
c.title,
p.id as 父节点id ,
p.title as 父节点名称
from
tb_menu c,
tb_menu p
where
c.parent=p.id
and
c.id=6
--⑤查询一个节点的所有父节点，即父亲节点，祖先节点(需要使用树形查询)
select * from tb_menu m start
with m.id
=50 connect
by PRIOR m.parent
=m.id
/*引入level和lpad实现分层显示*/
select level, lpad(
‘ ‘,
2 * level - 1)
|| m.id
from tb_menu m
start with m.id
= 1
connect by prior m.id
= m.parent
/*
prior就是表示上一条记录的意思，即prior m.parent=m.id就是表示上一条记录的父id是本条记录的id
也就是本条记录是上一条记录的父亲，那么就是在查询所有记录的父节点/祖先节点，反之。
*/
--⑥查询一个节点(一级子菜单6_1)的兄弟节点(亲兄弟)
select * from tb_menu m
where exists (
select * from tb_menu m2
where m.parent
=m2.parent
and m2.id
=6)
/*从SQL语句理解就是：查询表的数据，数据符合条件，在m2只要存在m.parent=m2.parent的数据，即取出*/
--⑦查询一个节点所有同级的节点（族兄弟）
with tmp
as(
select a.
* ,
level leaf
from tb_menu a start
with a.parent
=0 connect
by prior a.id
=a.parent
)
select * from tmp
where leaf
=(
select leaf
from tmp
where id
=50)
/*在SQL语句⑦中使用了level来标识每个节点在表中的级别【level表示递归的层次，即查询的深度】，使用了with语法来虚拟出了一张带有级别的临时表*/
/*=========================【补充】with语法：==================================
要求Oracle版本Oracle 9i及以上
其实就是把一些重复用到的SQL语句放在with as里面，取一个别名，后面的查询中就可以使用这个别名，对大批量的SQL
语句起到一个优化的作用。
eg1：
with t1 as (select * from emp e where e.ename like ‘%N%‘)
select * from t1;
eg2:
with
t1 as (select * from emp),
t2 as (select * from dept)
select * from t1,t2 where t1.deptno=t2.deptno
对于union all通常使用with as提升效率
因为union all的每个执行部分可能相同，如果每个部分都执行一遍成本太高，如果使用with as短语只执行一次即可
eg3:
with
t1 as (select t.name as name from table1 t where t.age>20),
t2 as (select s.lastname as name from table2 s where exists(select s.name from table3 p where s.id=p.id and p.id=‘a001‘))
select * from t1
union all
select * from t2
=====================================================================*/
--⑧查询一个节点的父节点的亲兄弟节点
/*
1、如果当前节点为最顶级的节点，即level=1,没有父节点，不考虑
2、如果当期节点为2级节点，即level=2,那么level=1的节点就是他的父节点和父节点的兄弟节点
3、如果level是3或者3以上，那么就要查询出来其祖父节点，再判断祖父节点的下一级（即父节点）是其父节点和父节点的兄弟节点
4、将结果集union在一起
*/
with tmp
as (
select m.
*,
level lev
from tb_menu m
start with m.parent
=0 connect
by m.parent
= prior m.id
)
select b.
* from tmp b ,(
select * from tmp
where id
=21 and lev
=2) a
where b.lev
=1
union all
select * from tmp
where
parent=(
select distinct x.id
from tmp x,
--祖父
tmp y,
--父亲
(
select * from tmp
where id
=21 and lev
>2) z
--儿子
where
y.id=z.parent
and x.id
=y.parent
)
--⑨查询一个节点的父节点的所有兄弟节点(族兄弟)
with tmp
as (
select m.
*,
level leaf
from tb_menu m
start with m.parent
=0 connect
by m.parent
= prior m.id
)
select * from tmp
where leaf
=(
select leaf
from tmp
where id
=6)
-1
--⑩列出全部路径，比如省/市/区/街道/社区
/*从顶部开始*/
select sys_connect_by_path(title,
‘/‘)
from tb_menu m
where id
=50 start
with parent
=0 connect
by parent
=prior id
/*从当前节点开始*/
select sys_connect_by_path(title,
‘/‘)
from tb_menu m start
with id
=50 connect
by prior parent
=id
/*
sys_connect_by_path函数是从start with开始的地方遍历，并记录下遍历到的节点。start with开始的地方被视为根节点，
将遍历到的路径根据函数中的分隔符组成一个新的字符串！
*/
/*
还有一些功能同样很强大的函数
connect_by_root函数用在列前，记录的是当前节点的根节点的内容
select connect_by_root title,tb_menu.* from tb_menu start with id=50 connect by prior parent=id
connect_by_isleaf函数用来判断当前节点是否包含下级节点，如果包含下级节点说明不是叶子节点(即没有子节点的节点，不管在第几级，只要没有子节点就是叶子节点)，返回0，
否则不包含，返回1
select connect_by_isleaf,tb_menu.* from tb_menu start with parent=0 connect by parent =prior id;
*/
Sql递归查询
标签：rac union all class sel 显示 数据 三级 number distinct
