1. 首页
  2. 问题
  3. MySQL Select和IF（）语句
小编典典

MySQL Select和IF（）语句

sql

我是MySQL的新手，需要帮助。我有一张桌子Invoices和一张桌子Payments。我在生成报告时会遇到麻烦，该报告将显示2019年12月31日之前已付款In Full或PartialPayment已收到的所有发票。一张发票可以通过一次或多次付款来支付（例如，部分付款，例如25％的首付，其余的则在工作完成时付款）。如何创建SQL查询，从中选择所有记录Invoices，然后为每个发票选择匹配的付款，将其与进行比较，Invoice Total并显示Paid in Full或partial Payment？我有以下代码：

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但是我有点迷路了。任何帮助将不胜感激！


阅读 215

收藏
2021-04-15

共1个答案

小编典典

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

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在该期间内，无需付款的允许发票。

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

  • 似乎您不需要表invoicestatus即可实现所需的结果。

2021-04-15