MySQL Select和IF()语句 [英] MySQL Select and IF() Statement

问题描述

我是MySQL的新手，我需要帮助.我有一张表 Invoices 和一张表 Payments .我在生成报告时遇到麻烦，该报告将显示在2019年12月31日之前收到的全部完整或 Partial Payment 支付的所有发票.一张发票可以通过一次或多次付款来支付(例如，部分付款，例如25％的首付，其余的则在工作完成时付款).如何创建SQL查询，该查询将从发票中选择所有记录，然后为每个发票选择匹配的付款，将其与发票总额进行比较，并显示 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()语句以显示全额支付和 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

注意:

• 左联接允许发票在该期间内没有任何付款.

• 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.

这篇关于MySQL Select和IF()语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！