表结构
field1 field2
Test testvalue1
Test testvalue2
SP spvalue1
SP spvalue2
CO covalue
查询结果
Test testvalue1 testvalue2
SP spvalue1 spvalue2
CO covalue null
...全文
933打赏收藏
忘记一条sql怎么写了,大概的意思如下
表结构 field1 field2 Test testvalue1 Test testvalue2 SP spvalue1 SP spvalue2 CO covalue 查询结果 Test testvalue1 testvalue2 SP spvalue1 spvalue2 CO covalue null
通常大家都认为这类问题无法用一句SQL解决,本来我也这么认为,可是今天无意中突然有了灵感,原来是可以这么做的:
前几天有人提到过sys_connect_by_path的用法,我想这里是不是也能用到这个方法,如果能做到的话,不用函数或存贮过程也可以做到了;要用到sys_connect_by_path,首先要自己构建树型的结构,并且树的每个分支都是单根的,例如1-〉2-〉3-〉4,不会存在1-〉2,1-〉3的情况;
我是这么构建树,很简单的,看下面的结果就会知道了:
SQL> select no,q,rn,lead(rn) over(partition by no order by rn) rn1
2 from (select no,q,row_number() over(order by no,q desc) rn from test)
3 /
有了这个树型的结构,接下来的事就好办了,只要取出拥有全路径的那个path,问题就解决了,先看no=‘001’的分组:
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = '001' and rn1 is null connect by rn1 = prior rn
SQL>
6 /
NO RESULT
---------- --------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001 ;n1;n2;n3
001 ;n1;n2;n3;n4
001 ;n1;n2;n3;n4;n5
上面结果的最后1条就是我们要得结果了
要得到每组的结果,可以下面这样
select t.*,
(
select max(sys_connect_by_path(q,';')) result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = t.no and rn1 is null connect by rn1 = prior rn
) value
from (select distinct no from test) t
SQL>
10 /
NO VALUE
---------- --------------------------------------------------------------------------------
001 ;n1;n2;n3;n4;n5
002 ;m1
003 ;t1;t2;t3;t4;t5;t6