Oracle递归查询（树形查询）

1.准备测试表和测试数据

--创建表
create table tb_menu(
    id number(10) not null,--主键ID
    pid number(10) not null,--父菜单ID
    title varchar2(50),--菜单名称
);

--添加数据

--父菜单
insert into tb_menu(id, pid,title ) values(1,0,'父菜单1');
insert into tb_menu(id, pid,title ) values(2,0,'父菜单2');
insert into tb_menu(id, pid,title ) values(3,0,'父菜单3');
insert into tb_menu(id, pid,title ) values(4,0,'父菜单4');
insert into tb_menu(id, pid,title ) values(5,0,'父菜单5');
--一级菜单
insert into tb_menu(id, pid,title) values(6,1,'一级菜单6');
insert into tb_menu(id, pid,title) values(7,1,'一级菜单7');
insert into tb_menu(id, pid,title) values(8,1,'一级菜单8');
insert into tb_menu(id, pid,title) values(9,2,'一级菜单9');
insert into tb_menu(id, pid,title) values(10, 2, '一级菜单10');
insert into tb_menu(id, pid,title) values(11, 2, '一级菜单11');
insert into tb_menu(id, pid,title) values(12, 3,'一级菜单12');
insert into tb_menu(id, pid,title) values(13, 3,'一级菜单13');
insert into tb_menu(id, pid,title) values(14, 3,'一级菜单14');
insert into tb_menu(id, pid,title) values(15, 4,'一级菜单15');
insert into tb_menu(id, pid,title) values(16, 4,'一级菜单16');
insert into tb_menu(id, pid,title) values(17, 4,'一级菜单17');
insert into tb_menu(id, pid,title) values(18, 5,'一级菜单18');
insert into tb_menu(id, pid,title) values(19, 5,'一级菜单19');
insert into tb_menu(id, pid,title) values(20, 5,'一级菜单20');
--二级菜单
insert into tb_menu(id, title, pid) values(21, '二级菜单21',6);
insert into tb_menu(id, title, pid) values(22, '二级菜单22',6);
insert into tb_menu(id, title, pid) values(23, '二级菜单23',7);
insert into tb_menu(id, title, pid) values(24, '二级菜单24',7);
insert into tb_menu(id, title, pid) values(25, '二级菜单25',8);
insert into tb_menu(id, title, pid) values(26, '二级菜单26',9);
insert into tb_menu(id, title, pid) values(27, '二级菜单27',10);
insert into tb_menu(id, title, pid) values(28, '二级菜单28',11);
insert into tb_menu(id, title, pid) values(29, '二级菜单29',12);
insert into tb_menu(id, title, pid) values(30, '二级菜单30',13);
insert into tb_menu(id, title, pid) values(31, '二级菜单31',14);
insert into tb_menu(id, title, pid) values(32, '二级菜单32',15);
insert into tb_menu(id, title, pid) values(33, '二级菜单33',16);
insert into tb_menu(id, title, pid) values(34, '二级菜单34',17);
insert into tb_menu(id, title, pid) values(35, '二级菜单35',18);
insert into tb_menu(id, title, pid) values(36, '二级菜单36',19);
insert into tb_menu(id, title, pid) values(37, '二级菜单37',20);
--三级菜单
insert into tb_menu(id, title, pid) values(38, '三级菜单38',21);
insert into tb_menu(id, title, pid) values(39, '三级菜单39',22);
insert into tb_menu(id, title, pid) values(40, '三级菜单40',23);
insert into tb_menu(id, title, pid) values(41, '三级菜单41',24);
insert into tb_menu(id, title, pid) values(42, '三级菜单42',25);
insert into tb_menu(id, title, pid) values(43, '三级菜单43',26);
insert into tb_menu(id, title, pid) values(44, '三级菜单44',27);
insert into tb_menu(id, title, pid) values(45, '三级菜单45',28);
insert into tb_menu(id, title, pid) values(46, '三级菜单46',28);
insert into tb_menu(id, title, pid) values(47, '三级菜单47',29);
insert into tb_menu(id, title, pid) values(48, '三级菜单48',30);
insert into tb_menu(id, title, pid) values(49, '三级菜单49',31);
insert into tb_menu(id, title, pid) values(50, '三级菜单50',31);
commit;

说明：pid字段存储的是当前菜单节点的上级id，如果菜单节点是顶级节点，该菜单没有上级菜单，则pid应为null，然而在表中记录最好不要为null，建议使用0代替，因为null记录会引起全文扫描。

2.语法说明

oracle中递归查询（树形查询）主要依托于：

select * from table_name [where 条件1] start with 条件2 connect by 条件3;

其中，[where 条件1] 可以不需要，[where 条件1]是对根据[start with 条件2 connect by 条件3]选择出来的记录进行过滤，是针对单条记录的过滤，不会考虑树形查询的树结构；
[start with 条件2 ]限定作为搜索起始点的条件，表示从满足什么条件的记录开始查询；[connect by 条件3]表示查询的连接条件，树形菜单的查询常常是因为记录与记录之间存在某种关系，这种关系通常就作为连接条件。

补充：connect by子句，通常跟关键字“PRIOR”一起使用，“PRIOR”关键字的位置通常有两种，”CONNECT BY PRIOR ID = PID”和”CONNECT BY ID = PRIOR PID”，关键字位置的不同，相应的会导致查询结果的不同。

3.递归查询（树形查询）实践

只有上面的文字说明可能还是搞不清楚树形查询，不过动手来操作一遍就清楚了。

我们从最基本的操作，逐步列出常见的树查询操作，所有的查询以家族中的辈分作比方。

1）查询树中的所有顶级父节点（家族中辈分最大的那一代人）。假如这个树是个目录结构，那么第一个操作总是找出所有的顶级节点，然后再逐个根据顶级节点找到其子节点。

select * from tb_menu m where m.pid = 0;

以上查询得到的就是树的所有顶级节点，也就是家族中辈分最大的那一代人。

2）查找一个节点的直属子节点（家族中某个长辈的所有儿子），此时查找的是直属的子类节点，也是用不到树形查询的。

select * from tb_menu m where m.pid=1;

3）查找一个节点的所有直属子节点（家族中某个长辈的所有直系子孙）。

select * from tb_menu m start with m.id=1 connect by m.pid=prior m.id;

4）查找一个节点的直属父节点（家族中的父亲），此时查找的是节点的直属父节点，也是用不到树形查询的

select c.id, c.title, p.id parent_id, p.title parent_title
    from tb_menu c, tb_menu p
    where c.pid=p.id and c.id=6

5）查找一个节点的所有直属父节点（家族中一个孩子的所有直系长辈祖先，比如父亲，祖父，……）

select * from tb_menu m start with m.id=38 connect by prior m.pid=m.id;

说明：
上面的3）和5），这两条查询都是树形查询，区别在于”prior”关键字的位置不同，所以决定了查询方式的不同，查询结果也不同。当 pid = prior id时，数据库会根据当前的id迭代出pid与该id相同的记录，所以查询的结果是迭代出了所有的子类记录；而prior pid = id时，数据库会跟据当前的pid来迭代出与当前的pid相同的id的记录，所以查询出来的结果就是所有的父类结果。
仔细看一下数据库的查询结果，可以发现，其实3）和5）的查询顺序也是不同的，3）是自上向下检索，5）是自下向上检索。

以下是一系列针对树结构的更深层次的查询，这里的查询不一定是最优的查询方式，或许只是其中的一种实现而已。

6)查询一个节点的兄弟节点（亲兄弟）

--m.parent=m2.parent-->同一个父亲
select * from tb_menu m
where exists (select * from tb_menu m2 where m.pid=m2.pid and m2.id=6)

7)查询与一个节点同级的节点（族兄弟）。 如果在表中设置了级别的字段，那么在做这类查询时会很轻松，同一级别的就是与那个节点同级的，在这里列出不使用该字段时的实现!

with tmp as
 (select a.*, level leaf
    from tb_menu a
   start with a.pid = 0
  connect by a.pid = prior a.id)
select * from tmp where leaf = (select leaf from tmp where id = 50);

这里使用两个技巧，一个是使用了level来标识每个节点在表中的级别，还有就是使用with语法模拟出了一张带有级别的临时表。

8)查询一个节点的父节点的的兄弟节点（伯父与叔父）

with tmp as(
    select tb_menu.*, level lev
    from tb_menu
    start with pid=0
    connect by pid = prior 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 pid = (select distinct x.id
                from tmp x, --祖父
                     tmp y, --父亲
                     (select *
                      from tmp
                      where id = 21 and lev > 2) z --儿子
                where y.id = z.pid and x.id = y.pid); 

这里查询分成以下几步。
首先，和第7个一样，将全表都使用临时表加上级别；
其次，根据级别来判断有几种类型，以上文中举的例子来说，有三种情况：
（1）当前节点为顶级节点，即查询出来的lev值为1，那么它没有上级节点，不予考虑。
（2）当前节点为2级节点，查询出来的lev值为2，那么就只要保证lev级别为1的就是其上级节点的兄弟节点。
（3）其它情况就是3以及以上级别，那么就要选查询出来其上级的上级节点（祖父），再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。
最后，就是使用union将查询出来的结果进行结合起来，形成结果集。

感谢以下网友的辛勤劳动，我只是一只无耻的伸手党 _

参考：

http://haohaoxuexi.iteye.com/blog/2099875；
http://www.cnblogs.com/baiyixianzi/archive/2012/08/30/plsql12.html(有不少错误)；
http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html；
http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html(全面，条理清晰)；
http://blog.csdn.net/chiclewu/article/details/15027245；
http://www.2cto.com/database/201204/126578.html；