关于分组后字段拼接的问题[行列转换]

关于分组后字段拼接的问题[行列转换]『By duanzilin』 

=========================================================== 

关于分组后字段拼接的问题[行列转换]『By duanzilin』

作者: lastwinner(http://lastwinner.itpub.net)

发表于: 2005.10.10 15:21

分类: Oracle , 

出处: http://lastwinner.itpub.net/post/7102/42717

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

 

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

大概是类似下列的情形：

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条就是我们要得结果了

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

 

原文地址

http://www.itpub.net/397706.html

 

 

 

代码:----------------------------------------------------------------------------

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)  ----------------------------------------------------------------------------

 

  SQL> 

  10 /

 

  NO VALUE

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

  001 ;n1;n2;n3;n4;n5

  002 ;m1

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

 

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

稍微改进下：代码:----------------------------------------------------------------------------

select no,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 rn1 is null 

 connect by rn1 = prior rn

 group by no;----------------------------------------------------------------------------代码:----------------------------------------------------------------------------

select no,max(sys_connect_by_path(q,';')) result from 

(

       select no,q,(row_number() over(order by no,q desc) + rank() over(order by no)) rn

       from test

)

 connect by rn-1 = prior rn

 group by no;----------------------------------------------------------------------------改进下算法，少一层嵌套查询，效率会好些: 呵呵，刚刚的算法有点问题，下面的应该没问题了

 

代码:------------------------------------------------------------------------------

select no,max(sys_connect_by_path(q,';')) result 

from (select no,q,(row_number() over(order by no,q desc) + dense_rank() over(order by no)) rn, 

             max(q) over(partition by no) qs

      from test

)

 start with q = qs

 connect by rn-1 = prior rn

 group by no;------------------------------------------------------------------------------lastwinner 发表于:2005.10.10 15:21 ::分类: ( Oracle , ) ::阅读:(1934次) :: 评论 (3)