问题提出：
一个高级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_lv
最近总结了一下oracle主键的设置方法，贴到这保存一下吧。 有两种方法可以设置主键，一种是自增长主键，另一种就是生成唯一序列。 一、自增长主键 --首先建一个表TEST
create tableTEST (
NID int PRIMARY KEY,
test1 varchar2(20),
test2 varchar2(20),
test3 ......
