ORACLE 10g中具有三列的字符串聚合

Question

这是一个示例表数据。

  Date | Fruit | Number
 -----------------------
   1   | Apple |   1
   1   | Apple |   2
   1   | Apple |   3
   1   |  Kiwi |   6
   1   |  Kiwi |  10
   2   | Apple |   4
   2   | Apple |   5
   2   | Apple |   6
   2   |  Kiwi |   4
   2   |  Kiwi |   7

我尝试将表列值连接起来，以获得以下内容：

  Date | Fruit | Number
 -----------------------
   1   | Apple |  1-2-3
   1   |  Kiwi |   6-10
   2   | Apple |  4-5-6
   2   |  Kiwi |    4-7

我使用的代码：

SELECT fruit,
       LTRIM( MAX(SYS_CONNECT_BY_PATH(number,','))
               KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS fruits_agg
  FROM( SELECT Date,
               fruit,
               number,
               ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
               ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) - 1 AS prev
          FROM table_name)

  GROUP BY Date,fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit AND Date = PRIOR Date
  START WITH curr = 1;

它不像我希望的那样工作。我哪里做错了？

PS:我使用的是10g版本，所以我不能使用listagg。

Answer 1

对于Oracle 10，使用您的方法-问题在于内部查询中的分区。

WITH tab as (
SELECT 1 as fdate,     'Apple' as fruit,    1 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    2 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    3 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    6 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    10 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    5 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    6 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    7 as num from dual )
SELECT fdate, fruit,LTRIM(MAX(SYS_CONNECT_BY_PATH(num,','))
    KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
    FROM   (SELECT fdate,
            fruit,
            num,
            ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDER BY num) AS curr,
            ROW_NUMBER() OVER (PARTITION BY fdate, fruit ORDER BY num) -1 AS prev
     FROM   tab)
  GROUP BY fdate,fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit AND fdate = PRIOR fdate
 START WITH curr = 1;

给予：

FDATE             FRUIT   FRUITS_AGG                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
1                "Kiwi"  "6,10"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
1                "Apple" "1,2,3"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
2                "Kiwi"  "4,7"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
2                "Apple" "4,5,6"

Oracle 11解决方案要简单得多：

WITH tab as (
SELECT 1 as fdate,     'Apple' as fruit,    1 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    2 as num from dual union
SELECT 1 as fdate,     'Apple' as fruit,    3 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    6 as num from dual union
SELECT 1 as fdate,     'Kiwi' as fruit,    10 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    5 as num from dual union
SELECT 2 as fdate,     'Apple' as fruit,    6 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    4 as num from dual union
SELECT 2 as fdate,     'Kiwi' as fruit,    7 as num from dual )
select fdate
     , fruit
     , listagg(num,'-') within group ( order by num ) fruit_agg
from tab
group by fdate, fruit 

返回：

FDATE  FRUIT    FRUIT_AGG
1      Kiwi      6-10
1      Apple     1-2-3
2      Kiwi      4-7
2      Apple     4-5-6