问题提出：
一个高级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 ......
今天电脑拿回家，启动oracle数据库的监听服务时发现无法启动。给出的错误提示是说，这个服务启动了又停止了，不知道是为什么。因为在学校的时候用是没有问题的。我想应该是因为ip的是问题。在网上查了一下：你是否修改你的机器名或IP地址，如果有修改，把ORACLE\ora81\network\admin\listener.ora文件中的host修改为新机器� ......
