问题提出:一个高级SQL语句问题
假设有一张表,A和B字段都是NUMBER,
A B
1 2
2 3
3 4
4
有这样一些数据
现在想用一条SQL语句,查询出这样的数据COLA COL1 COL3 COL2 COL1
4 4 3 2 1就是说,A和B的数据表示一种连接的关系,现在想通过A的一个值,去查询A所对应的B值,直到B为NULL为止,然后按照根到叶子显示
假设有一张表,A和B字段都是NUMBER,
A B
1 2
2 3
3 4
4
有这样一些数据
现在想用一条SQL语句,查询出这样的数据COLA COL1 COL3 COL2 COL1
4 4 3 2 1就是说,A和B的数据表示一种连接的关系,现在想通过A的一个值,去查询A所对应的B值,直到B为NULL为止,然后按照根到叶子显示
你那个COLA COL1 COL3 COL2 COL1分别代表什么意思
COLA就是指定的A列的值?
COLA 对应的是 根的值 COL4 COL3 COL2 COL1 依次是从根到 叶子 中间的所有节点
select 1 a,2 b 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 7,8 from dual
union all select 8,9 from dual)
select root cola,
max(decode(l,1,a))col1,
max(decode(l,2,a))col2,
max(decode(l,3,a))col3,
max(decode(l,4,a))col4
from(
select a,connect_by_root a root,level l from tt
start with a in(4,7)
connect by b=prior a)
group by root
2 select 1 a,2 b from dual
3 union all select 2,3 from dual
4 union all select 3,4 from dual
5 union all select 4,null from dual
6 union all select 1,10 from dual --这两条记录是新加的 如果你在下面的in子句里不加10 那么就得不到正确结果
7 union all select 10,null from dual
8 union all select 5,6 from dual
9 union all select 6,7 from dual
10 union all select 7,8 from dual
11 union all select 8,9 from dual)
12 select root cola,
13 max(decode(l,1,a))col1,
14 max(decode(l,2,a))col2,
15 max(decode(l,3,a))col3,
16 max(decode(l,4,a))col4
17 from(
18 select a,connect_by_root a root,level l from tt
19 start with a in(4,7,10)
20 connect by b=prior a)
21 group by root
22 ; COLA COL1 COL2 COL3 COL4
---------- ---------- ---------- ---------- ----------
4 4 3 2 1
7 7 6 5
10 10 1
with tt as(
select 1 a,2 b 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 5,10 from dual
union all select 10,11 from dual
union all select 6,7 from dual
union all select 12,7 from dual
union all select 7,8 from dual
union all select 8,9 from dual)
select root cola,
substr(max(str),2)str
from(
select a,connect_by_root a root,rownum-level flag,sys_connect_by_path(a,',')str from tt
start with a in(4,7)
connect by b=prior a)
group by root,flag