Oracle 树的应用

来源：互联网发布：比斯利在雄鹿的数据编辑：程序博客网时间：2024/05/21 10:54

问题提出：

一个高级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 dualunion 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 =2THEN 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 =3THEN 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 =4THEN 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 =5THEN 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

显示结果如下：

ATREE_LVLROOTTREE_LVL_PATHCOL01COL02COL03COL04COL0510444,3,2,04320　21444,3,2,14321　32344,3,2432　　43244,343　　　541444　　　　65377,6,5765　　76277,676　　　871777　　　　912277,12712　　　