mysql查询:按日期显示所有已付款和发票总数的摘要 [英] mysql query: show summary of all payments made and total of invoices by date

问题描述

我想要这种结果

.

我什至不知道如何用php做到这一点.我什至试图按时加入付款和发票表，但徒劳无功.

I even can't figure out how to do it with php. I even tried to join payment and invoice table on date but in vain.

这是一个购买系统，此查询将按日期显示所有已付款和发票总数的摘要.

It's a purchase system and this query will show summary of all payments made and total of invoices by date.

我想到了一个解决方案，该解决方案首先从发票中选择所有日期，然后从付款中选择所有日期并合并.然后检查该日期是否有发票，然后检查该日期是否有付款.但是这样一来，查询就会太多了.

I thought of a solution that first select all dates from invoices and then select all dates from payments and take their union. Then check if there is an invoice on that date and then check if there is a payment on that date. But this way there will be too many queries.

推荐答案

select
  x.date,
  sum(x.invoiceTotal) as invoiceTotal,
  sum(x.paymentsMade) as paymentMade
from
 (select
   i.date, 
   sum(i.rate * i.quantity /*?*/) as invoiceTotal,
   null as paymentMade    
 from
   invoice i
   inner join invoiceitem ii on ii.invoiceId = i.invoiceId
 group by
   i.date
 union all
 select
   p.date,
   null as invoiceTotal,
   sum(p.amount) as paymentMade    
 from
   payment p
 group by
   p.date) x
group by
  x.date
order by
  x.date

这篇关于mysql查询:按日期显示所有已付款和发票总数的摘要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！