Oracle SQL - 结合多个表结果集中一列中同一列中的值

Question

我想知道如何将来自同一列的多个记录从两个不同的表中合并到一个表中。例如下面是我的表：

注表：

INVOICE NOTES 
1000 REPLACE PUMP 
1000 REPLACE OIL 
1000 REPLACE FILTER 
1111 WO# 123 
1111 REPLACE GASKET 
1234 REPLACE OIL 

发票表：

INVOICE AMOUNT 
1000 100 
1111 50 
1234 20 

我可以运行此查询，并将结果：

SELECT INV.INVOICE, INV.AMOUNT FROM INVOICE INV 
INNER JOIN NOTES ON INV.INVOICE = NOTES.INVOICE 

但我会比如将结果集中一列中的每张发票的注释合并在一起。对于例子我的结果应该是这样的

INVOICE AMOUNT  NOTES 
1000  100  REPLACE PUMP, REPLACE OIL, REPLACE FILTER 
1111  50   WO# 123,REPLACE GASKET 
1234  20   REPLACE OIL 

Answer 1

documentation - listagg

查询

with inv_notes as 
(select 1000 as INVOICE, 'REPLACE PUMP' as notes from dual union all 
select 1000, 'REPLACE PUMP' from dual union all 
select 1000, 'REPLACE OIL' from dual union all 
select 1000, 'REPLACE FILTER' from dual union all 
select 1111, 'WO# 123' from dual union all 
select 1111, 'REPLACE GASKET' from dual union all 
select 1234, 'REPLACE OIL' from dual 
) 
, 
inv_amount as (
select 1000 as INVOICE, 100 AMOUNT from dual union all 
select 1111, 50 from dual union all 
select 1234, 20 from dual) 
select a.invoice, a.amount, listagg(n.notes, ',') WITHIN GROUP (ORDER BY a.invoice, a.amount) notes 
from inv_notes n 
inner join inv_amount a on n.invoice = a.invoice 
group by a.invoice, a.amount 

结果

1 1000 100 REPLACE FILTER,REPLACE OIL,REPLACE PUMP,REPLACE PUMP 
2 1111 50 REPLACE GASKET,WO# 123 
3 1234 20 REPLACE OIL