MySQL Select 和 IF() 语句

2022-01-09 00:00:00 sql sum left-join mysql aggregate-functions

我是 MySQL 的新手，我需要帮助.我有一个表 Invoices 和一个表 Payments.我无法生成一份报告，该报告将显示在 2019 年 12 月 31 日之前以 In Full 支付的所有发票或 Partial Payment 收到的所有发票.一张发票可以通过一笔或多笔付款来支付(例如，部分付款，例如 25% 的首付，其余部分在工作完成时付款).如何创建 SQL 查询，从 Invoices 中选择所有记录，然后为每个 Invoice 选择匹配的 Payment，将其与 Invoice Total 进行比较，然后显示 Paid全额支付还是部分支付?我有以下代码:

I'm kind of new to MySQL and I need help. I have a table Invoices and a table Payments. I am having trouble generating a report that will show all invoices that were paid in In Full or a Partial Payment was received by 12/31/2019. One Invoice can be paid by one or more payments (For example a partial payment, such as 25% down, and the remainder payment on completion of work). How can I create SQL query that will select ALL records from Invoices and then for each Invoice select matching Payment, compare it to the Invoice Total and will display either Paid in Full or partial Payment? I have the following code:

SELECT Invoices.InvoiceID, Invoices.ClientName, Invoices.InvoiceTotal
INNER JOIN InvoiceStatus ON InvoiceStatus.InvoiceStatusID = Invoices.InvoiceStatus
WHERE InvoiceDate BETWEEN '2019-1-1  00:00:00' AND '2019-12-31 23:59:59'
AND (Invoices.InvoiceStatus = '1' OR Invoices.InvoiceStatus = '2')
 AND (Invoices.InvoiceActive != 0 OR Invoices.InvoiceActive IS NULL)
ORDER BY ClientName

SELECT Payment.PaymentID, Payment.PaymentReceivedAmount, Payment.PaymentReceivedDate FROM `Payment` 
INNER JOIN Invoices ON Invoices.InvoiceID = Payment.InvoiceID
WHERE PaymentReceivedDate BETWEEN '2019-1-1  00:00:00' AND '2019-12-31 23:59:59'

如果我执行 INNER JOIN，那么我会得到两行以支付两次付款的发票.我知道我还需要实现 IF() 语句来显示 Paid in Full 和 Partial Payment 但我有点迷茫.任何帮助将不胜感激！

If I do INNER JOIN then I get two rows for an Invoice that was paid with two payment. I know that I also need to implement IF() statement to show Paid in Full and Partial Payment but I'm a little lost. Any help would be greatly appreciated!

推荐答案

您可以连接两个表，按发票汇总，并使用 sum() 计算总付款.最后，可以使用 case 表达式来显示状态:

You can join both tables, aggregate by invoice, and use sum() to compute the total payment. Finally, a case expression can be used to display the status:

select i.invoiceid, i.clientname, i.invoicetotal, 
    coalesce(sum(p.PaymentReceivedAmount), 0) as paymenttotal,
    case when i.invoicetotal <=> sum(p.PaymentReceivedAmount) then 'In Full' else 'Partial Payment' end as paymentstatus
from invoices i
left join payment p 
    on  p.invoiceid = i.invoiceid
    and p.paymentreceiveddate >= '2019-01-01' and p.paymentreceiveddate < '2020-01-01'
where 
    i.invoicedate >= '2019-01-01' and i.invoicedate < '2020-01-01'
    and i.invoicestatus in (1, 2)
    and (i.invoiceactive <> 0 or i.invoiceactive is null)
group by i.invoiceid
order by clientname

注意事项:

  • left join 允许在此期间无需任何付款的发票.

  • The left join allows invoices without any payment in the period.

我使用了与您原始查询中相同的日期过滤器，但通过将其设置为半开间隔对其进行了一些优化.

I used the same date filters as in your original queries, but optimized it a little by turning it to half-open intervals.

您似乎不需要表 invoicestatus 来达到您想要的结果.

It does not seem like you need table invoicestatus to achieve the result you want.

相关文章