/*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;
*/
