SQL从子查询中获取一个以逗号分隔的值的列

I have these tables in my postresql:-
1.Payment(id, number, amount)
2.Invoice(id, number, amount)
and a invoice_payment table to allocate payments amount to invoices
3.InvoicePayment(id, payment_id, invoice_id, amount)
Single payment can be allotted to many invoices. Now I want payment details, but I also want invoices.number of the invoices this payment is allotted to. I am doing something like this:-

我在postresql中有这些表: - 1.Payment(id,number,amount)2.Invoice(id,number,amount)和invoice_payment表,用于将付款金额分配给发票3.InvoicePayment(id,payment_id,invoice_id,amount )单笔付款可以分配给许多发票。现在我想要付款详细信息,但我也想要发票。这笔付款的发票数量是分配的。我这样做: -

SELECT
    payments.number,
    payments.amount,
    (SELECT invoices.number from invoices INNER JOIN invoice_payments
     ON invoices.id = invoice_payments.invoice_id
     WHERE invoice_payments.payment_id = payments.id)
from payments

But it gives me error more than one row returned by a subquery. How do I make the invoice number comma-seperated for every payments row? Sample data:-

但是它给了我一个子查询返回的多行错误。如何为每个付款行以逗号分隔发票编号?样本数据:-

Payment  
id  number  amount  
1  "Pay001" 100  
2 "Pay002" 150  
3 "Pay003" 150 
Invoice  
id number amount  
1 "INV001" 100  
2 "INV002" 200  
3 "INV003"  100  
InvoicePayment  
id payment_id invoice_id amount  
1    1          1          50  
2    1          2          50  
3    2          2          150  
4    3          1           50  
5    3          3          100  

Result:-  

payment_id payment_number amount invoices  
1          "Pay001"       100    "INV001, INV002"  
2          "Pay002"       150    "INV002"  
3          "Pay003"       150    "INV001, INV002"

2 个解决方案

#1

You can use string_agg function in postgres to concatenate rows, separated by chosen delimiter.

您可以在postgres中使用string_agg函数来连接行,由选定的分隔符分隔。

SELECT
    payments.number,
    payments.amount,
    (SELECT string_agg(invoices.number,',') from invoices INNER JOIN invoice_payments
     ON invoices.id = invoice_payments.invoice_id
     WHERE invoice_payments.payment_id = payments.id)
from payments

#2

Try this

SELECT
    payments.number,
    payments.amount,
    inv_p.inv_no
from payments p inner join (Select invoice_payments.payment_id, string_agg(invoices.number,',') as inv_no
                            From invoices inner join invoice_payments on (invoices.id = invoice_payments.invoice_id)
                            Group by invoice_payments.payment_id) inv_p on (p.id = inv_p.payment_id)