oracle group 拼接,原创＋突发奇想＋分享－－－－－关于分组后字段拼接的问题...

最近在论坛上，经常会看到关于分组后字段拼接的问题，

大概是类似下列的情形：

SQL> select no,q from test

2  /

NO         Q

---------- ------------------------------

001        n1

001        n2

001        n3

001        n4

001        n5

002        m1

003        t1

003        t2

003        t3

003        t4

003        t5

003        t6

12 rows selected

最后要得到类似于如下的结果：

001        n1;n2;n3;n4;n5

002        m1

003        t1;t2;t3;t4;t5;t6

通常大家都认为这类问题无法用一句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  /

NO         Q                                      RN        RN1

---------- ------------------------------ ---------- ----------

001        n5                                      1          2

001        n4                                      2          3

001        n3                                      3          4

001        n2                                      4          5

001        n1                                      5

002        m1                                      6

003        t6                                      7          8

003        t5                                      8          9

003        t4                                      9         10

003        t3                                     10         11

003        t2                                     11         12

003        t1                                     12

12 rows selected

有了这个树型的结构，接下来的事就好办了，只要取出拥有全路径的那个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条就是我们要得结果了

要得到每组的结果，可以下面这样

[PHP]

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

[/PHP]

SQL>

10  /

NO         VALUE

---------- --------------------------------------------------------------------------------

001        ;n1;n2;n3;n4;n5

002        ;m1

003        ;t1;t2;t3;t4;t5;t6

对上面结果稍加处理就可以了，希望对大家有帮助：)