Oracle 树的应用

问题提出：

一个高级SQL语句问题
假设有一张表，有这样一些数据

a b

1 2
0 2
2 3
3 4
4 
5 6
6 7
12 7
7 

 

a是子节点 b是父节点 ,如果b为null 则为一棵树的根节点

根据A与B的这个关系 找出一棵树的结构显示出来 ，并显示出每个节点在书中的层级，将树的结构打印出来

 

我这边假设树最多有5个层级，SQL如下：

with tt as( select 1 a,2 b from dual union all SELECT 0,2 from dual union all select 2,3 from dual union all select 3,4 from dual union all select 4,null from dual union all select 5,6 from dual union all select 6,7 from dual union all select 12,7 from dual union all select 7,null from dual) SELECT a ,tree_lvl ,root ,tree_lvl_path ,(CASE WHEN tree_lvl >1 THEN SUBSTR(tree_lvl_path,1,instr(tree_lvl_path,',',1,1)-1 ) ELSE tree_lvl_path END) COL01 ,(CASE WHEN tree_lvl <2 THEN NULL WHEN tree_lvl =2 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,1)+1 ) WHEN tree_lvl >2 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,1)+1,instr(tree_lvl_path,',',1,2)- instr(tree_lvl_path,',',1,1)-1 ) END) COL02 ,(CASE WHEN tree_lvl <3 THEN NULL WHEN tree_lvl =3 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,2)+1 ) WHEN tree_lvl >3 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,2)+1,instr(tree_lvl_path,',',1,2)- instr(tree_lvl_path,',',1,1)-1 ) END) COL03 ,(CASE WHEN tree_lvl <4 THEN NULL WHEN tree_lvl =4 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,3)+1 ) WHEN tree_lvl >4 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,3)+1,instr(tree_lvl_path,',',1,3)- instr(tree_lvl_path,',',1,2)-1 ) END) COL04 ,(CASE WHEN tree_lvl <5 THEN NULL WHEN tree_lvl =5 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,4)+1 ) WHEN tree_lvl >5 THEN SUBSTR(tree_lvl_path,instr(tree_lvl_path,',',1,4)+1,instr(tree_lvl_path,',',1,5)- instr(tree_lvl_path,',',1,4)-1 ) END) COL05 FROM ( select a, tree_lvl, root , max(tree_lvl_path) tree_lvl_path from( select a,connect_by_root a root,LEVEL tree_lvl,ltrim(sys_connect_by_path(a,','),',') tree_lvl_path from tt start with a in(4,7) connect by b=prior a) group BY a, tree_lvl, root ) ORDER BY root ,a

 

显示结果如下：

	A	TREE_LVL	ROOT	TREE_LVL_PATH	COL01	COL02	COL03	COL04	COL05
1	0	4	4	4,3,2,0	4	3	2	0
2	1	4	4	4,3,2,1	4	3	2	1
3	2	3	4	4,3,2	4	3	2
4	3	2	4	4,3	4	3
5	4	1	4	4	4
6	5	3	7	7,6,5	7	6	5
7	6	2	7	7,6	7	6
8	7	1	7	7	7
9	12	2	7	7,12	7	12