时间戳

首页 > 解决方案 > MySQL Select 和 IF() 语句

mysql - MySQL Select 和 IF() 语句

问题描述

我是 MySQL 的新手，我需要帮助。我有一张桌子Invoices和一张桌子Payments。我无法生成一份报告，该报告将显示 2019 年 12 月 31 日之前已支付In Full或Partial Payment已收到的所有发票。一张发票可以通过一笔或多笔付款来支付（例如，部分付款，例如 25% 的首付，其余部分在工作完成时付款）。如何创建 SQL 查询，该查询将从中选择所有记录Invoices，然后为每个 Invoice 选择匹配的 Payment，将其与Invoice Total和将显示Paid in Fullor进行比较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但我有点迷茫。任何帮助将不胜感激！

标签： mysqlsqlsumleft-joinaggregate-functions

解决方案

您可以连接这两个表，按发票汇总，并用于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来达到您想要的结果。

推荐阅读