Oracle 树操作(select…start with…connect by…prior)



Oracle 树操作(select…start with…connect by…prior)

oracle树查询的最重要的就是select…start with…connect by…prior语法了。依托于该语法，我们可以将一个表形结构的以树的顺序列出来。在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等，在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。

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

1

3

5

7

9

11

13

15

17

19

21

23

25

27

29

31

33

35

37

39

41

43

45

47

49

51

53

55

57

59

61

63

table not , --标题

   parent number(10)  

--父菜单

insert  tb_menu(id, title, parent) (1, , );

insert  tb_menu(id, title, parent) (2, , );

insert  tb_menu(id, title, parent) (3, , );

insert  tb_menu(id, title, parent) (4, , );

insert  tb_menu(id, title, parent) (5, , );

--一级菜单

insert  tb_menu(id, title, parent) (6, ,1);

insert  tb_menu(id, title, parent) (7, ,1);

insert  tb_menu(id, title, parent) (8, ,1);

insert  tb_menu(id, title, parent) (9, ,2);

insert  tb_menu(id, title, parent) (10, ,2);

insert  tb_menu(id, title, parent) (11, ,2);

insert  tb_menu(id, title, parent) (12, ,3);

insert  tb_menu(id, title, parent) (13, ,3);

insert  tb_menu(id, title, parent) (14, ,3);

insert  tb_menu(id, title, parent) (15, ,4);

insert  tb_menu(id, title, parent) (16, ,4);

insert  tb_menu(id, title, parent) (17, ,4);

insert  tb_menu(id, title, parent) (18, ,5);

insert  tb_menu(id, title, parent) (19, ,5);

insert  tb_menu(id, title, parent) (20, ,5);

--二级菜单

insert  tb_menu(id, title, parent) (21, ,6);

insert  tb_menu(id, title, parent) (22, ,6);

insert  tb_menu(id, title, parent) (23, ,7);

insert  tb_menu(id, title, parent) (24, ,7);

insert  tb_menu(id, title, parent) (25, ,8);

insert  tb_menu(id, title, parent) (26, ,9);

insert  tb_menu(id, title, parent) (27, ,10);

insert  tb_menu(id, title, parent) (28, ,11);

insert  tb_menu(id, title, parent) (29, ,12);

insert  tb_menu(id, title, parent) (30, ,13);

insert  tb_menu(id, title, parent) (31, ,14);

insert  tb_menu(id, title, parent) (32, ,15);

insert  tb_menu(id, title, parent) (33, ,16);

insert  tb_menu(id, title, parent) (34, ,17);

insert  tb_menu(id, title, parent) (35, ,18);

insert  tb_menu(id, title, parent) (36, ,19);

insert  tb_menu(id, title, parent) (37, ,20);

--三级菜单

insert  tb_menu(id, title, parent) (38, ,21);

insert  tb_menu(id, title, parent) (39, ,22);

insert  tb_menu(id, title, parent) (40, ,23);

insert  tb_menu(id, title, parent) (41, ,24);

insert  tb_menu(id, title, parent) (42, ,25);

insert  tb_menu(id, title, parent) (43, ,26);

insert  tb_menu(id, title, parent) (44, ,27);

insert  tb_menu(id, title, parent) (45, ,28);

insert  tb_menu(id, title, parent) (46, ,28);

insert  tb_menu(id, title, parent) (47, ,29);

insert  tb_menu(id, title, parent) (48, ,30);

insert  tb_menu(id, title, parent) (49, ,31);

insert  tb_menu(id, title, parent) (50, ,31);

commit  

select  from 

null 1 1  2 4 --c-->child, p->parent select c.id, c.title, p.id parent_id, p.title parent_title from tb_menu c, tb_menu p where c.parent=p.id and c.id= 38  2 6 2 4 6 8 with tmp as(        from tb_menu a                       null             50 2 4 6 8 10 12 14 16 18 20 22 select tb_menu.*, level lev      start with parent is connect by parent = prior id)      select b.* from tmp b,(select *              where id = and lev = ) a where b.lev =   union all from tmp x, --祖父                       (select *                        where id = and lev > ) z --儿子                  2 4 6 8 with tmp as(        from tb_menu a                       null             61 2 4 '/' 50 start with parent is 2 4 select sys_connect_by_path (title, ) from tb_menu start with id = 2 4 select connect_by_root title, tb_menu.* from tb_menu start with id = 2 4 select connect_by_isleaf, tb_menu.* from tb_menu start with parent is <code style='background: none !important; margin: 0px !important; padding: 0px !important; outline: 0px !important; border-radius: 0px !important; border: 0px currentColor !important; border-image: none !important; left: auto !important; top: auto !important; width: auto !important; height: auto !important; right: auto !important; bottom: auto !important; color: rgb(0, 0, 0) !important; line-height: 1.8em !important; overflow: visible !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; vertical-align: baseline !important; float: none !important; white-space: pre-wrap; position: static !important; min-height: auto !important; box-sizing: content-box !important;' plain"="">connect by parent = prior id; connect_by_isleaf函数用来判断当前节点是否包含下级节点，如果包含的话，说明不是叶子节点，这里返回0；反之，如果不包含下级节点，这里返回1。 至此，oracle树型查询基本上讲完了，以上的例子中的数据是使用到做过的项目中的数据，因为里面的内容可能不好理解，所以就全部用一些新的例子来进行阐述。以上所有sql都在本机上测试通过，也都能实现相应的功能，但是并不能保证是解决这类问题的最优方案（如第8条明显写成存储过程会更好）. 转载自：http://www.cnblogs.com/walk-the-Line/p/4882866.html 感谢 学无止境