三列ORACLE 10g中的字符串聚合 [英] String Aggregation in ORACLE 10g with three columns
本文介绍了三列ORACLE 10g中的字符串聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是样本表数据
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
我尝试将表的列值连接起来以获取以下信息:
I try to concatenate the table column values to get the following:
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;
它不符合我的期望.我在哪里做错了?
It doesn't work the way I want it to be. Where Did I do wrong?
PS:我的版本是10g，所以我不能使用listagg.
PS: I'm on version 10g, so I can't use listagg.
推荐答案
对于Oracle 10，使用您的方法-问题是内部查询中的分区.
For Oracle 10, using your approach - the issue is the partitioning in your inner query.
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解决方案要简单得多:
The Oracle 11 solution is a whole lot easier:
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
这篇关于三列ORACLE 10g中的字符串聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
