mysql 查询：按日期显示所有付款的摘要和发票总数

I want this kind of result

从这些表中。

我什至不知道如何用 php 来做到这一点。我什至尝试按日期加入付款和发票表，但没有成功。

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

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

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