如何在预言机中连接连续的行

Question

我是甲骨文的初学者。我尝试按照源代码来解决并得到以下O/P.但是不能，请给出一些想法来解决这个问题。

id    product                      sales
---  -------------                --------
1      Soaps                     1200
2      Ice cream                 2300
3      Cool drinks               2500
4      Tv                        5000
5      Mobile                   10000
6      Washing machine          20000```
```O/P
id    product                   sales
---  -------------             --------
1      Soaps                     1200
2      Ice cream+Cool drinks     4800
3      Tv+Mobile                15000 
6      Washing machine          20000```

Answer 1

必须有一个类别和产品映射表。 必须映射具有类别的产品才能解决您的问题。

Select min(t.id) as id,
Listagg(t.product, ' + ') within group (order by t.id) as product,
Sum(t.sales) as sales
From your_table t
Join mapping_table m
On (m.product = t.product)
Group by m.catrgory;

干杯！！

Answer 2

您可以使用lead()分析函数：

with t1 as
(
select id, 
concat(concat(product,'+'),lead(product) over (order by id)) as product,
sales + lead(sales) over (order by id) as sales
from tab -- your original table
), t2 as
(
select *
from t1
where id in (2,4) 
union all
select *
from tab
where id in (1,6) 
)
select * 
from t2
order by id;

演示

Answer 3

我的事情，您需要为分组添加列。请尝试：

WITH temp as (SELECT  1 id, 1 group_id, 'Soaps' str, 1200  as  price FROM dual
UNION ALL
SELECT  2 id, 2, 'Ice cream', 2300  FROM dual
UNION ALL
SELECT  3 id, 2, 'Cool drinks', 2300  FROM dual
UNION ALL
SELECT  4 id, 3, 'Tv', 5000  FROM dual
UNION ALL
SELECT  5 id, 3, 'Mobile', 10000  FROM dual
UNION ALL
SELECT  6 id, 4, 'Washing machine', 20000  FROM dual)
SELECT group_id, LISTAGG(str, ', ')
WITHIN GROUP (ORDER BY group_id) "str",
sum(price) price
FROM temp
GROUP BY  group_id

结果：

1   Soaps                   1200
2   Cool drinks, Ice cream  4600
3   Mobile, Tv              15000
4   Washing machine         20000