java sql 递归查询_Sql递归查询

/*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 TABLEtb_menu(

idnumber(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的所有子孙后代*/

--④查询一个节点的直属父节点(用不到树形查询)

selectc.id,

c.title,

p.idas父节点id ,

p.titleas父节点名称fromtb_menu c,

tb_menu pwherec.parent=p.idandc.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.idfromtb_menu m

startwith m.id = 1connectby 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.* ,levelleaffrom 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.*,levellevfromtb_menu m

startwith 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 * fromtmpwhereparent=(select distinctx.idfrom tmp x,--祖父

tmp y,--父亲

(select * from tmp where id=21 and lev>2) z--儿子

wherey.id=z.parent and x.id=y.parent

)--⑨查询一个节点的父节点的所有兄弟节点(族兄弟)

with tmp as(select m.*,levelleaffromtb_menu m

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