Oracle 数据合并的解决办法(用connect by 树解决)

首先来说说问题的需求: (是csdn论坛里的问题)

示例如下，表invoice_test：

order_num   customer
263663               A
273631               A
163263               B
173636               B
553024               A

想得到这样的结果：

customer      order_num
A         263663;273631;553024
B         163263;173636

SQL:

     SELECT DISTINCT yy.customer,
                     ltrim(first_value(path)
                           over(PARTITION BY yy.customer ORDER BY yy.lev DESC),
                           ';') order_num
       FROM (SELECT tt.*,
                    LEVEL lev,
                    sys_connect_by_path(order_num, ';') path
               FROM (SELECT it.customer ||
                            (row_number() over(PARTITION BY it.customer ORDER BY
                                               it.customer)) a,
                            it.customer ||
                            (row_number() over(PARTITION BY it.customer ORDER BY
                                               it.customer) - 1) b,
                            it.*
                       FROM invoice_test it
                      ORDER BY it.customer) tt
             CONNECT BY PRIOR a = b) yy;

 

应该有点问题关于是否可用rownum?

答案是否。why?

因为把 row_number() over(PARTITION BY it.customer ORDER BY it.customer) 改为 rownum 的话，那么connect by 就无法通过父子结构去遍历数据。rownum它是伪列，它是按照当初创建表时数据插入的顺序生成的。

以下是用rownum产生的结果:

SQL> SELECT
  2          it.customer||rownum c,
  3          it.customer ||(rownum-1) p,
  4            customer,
  5           order_num
  6        FROM
  7          invoice_test it
  8          order by customer
  9  ;

C                                                            P                                                            CUSTOMER             ORDER_NUM
------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------
A1                                                           A0                                                           A                       263663
A2                                                           A1                                                           A                       273631
A5                                                           A4                                                           A                       553024
B3                                                           B2                                                           B                       163263
B4                                                           B3                                                           B                       173636

ths...

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/mantisXF/archive/2007/07/19/1699067.aspx