这是一个示例表数据
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。
对于Oracle10，使用您的方法-问题是内部查询中的分区。
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