mysql - 从多个表中检索数据的 SQL 查询

Question

这是正确的答案:

SELECT 
    p.product_name,
    coalesce((sum((i.time_paid is null and i.time_canceled is null and i.time_refunded is null) * it.line_total_price)), 0) due,
    coalesce((sum((i.time_paid is not null) * it.line_total_price)), 0) paid,
    coalesce((sum((i.time_canceled is not null) * it.line_total_price)), 0) canceled,
    coalesce((sum((i.time_refunded is not null) * it.line_total_price)), 0) refunded
FROM
    PRODUCT p
LEFT JOIN invoice_item it ON p.id = it.product_id
LEFT JOIN invoice i ON it.invoice_id = i.id
GROUP BY
    p.id, p.product_description

我必须用当前代码添加的只是添加 null 和 * total_price。

谢谢!

Answer 1

我认为你想要条件聚合。逻辑没有很好地指定(特别是对于 due 列)，但我认为这应该接近您想要的:

select 
    p.description,
    coalesce(sum((i.time_paid is null and i.time_cancelled is null and i.time_refund is null) * ii.line_total_price)), 0) due,
    coalesce(sum((i.time_paid is not null) * ii.line_total_price)), 0) paid,
    coalesce(sum((i.time_cancelled is not null) * ii.line_total_price)), 0) cancelled,
    coalesce(sum((i.time_refunded is not null) * ii.line_total_price)), 0) refunded
from products p
left join invoice_item ii on ii.product_id = p.id
left join invoices i on i.id = ii.invoice_id
group by p.id, p.description