mysql - 尝试获取慈善组织的捐款和支出的月度报告

我有两个表格，分别称为费用和捐款

表格1

花费

s.no   expensedate    expense
1       13/01/16         30
2       15/01/16         60

表2

s.no   donationdate    donation
15       14/01/16         30
18       19/01/16         10

需要输出

Month       Expense    Donation
Jan            90            40

我可以像这样单独获得输出

Month  expense
jan       90

要么

Month  donation
jan       40

但无法将它们连接在一起，因此可以提供所需的输出。

谢谢

最佳答案

我可以使用union all和group by来做到这一点：

select date_format(dte, '%Y-%m') as yyyymm,
       coalesce(sum(expense), 0) as expenses,
       coalesce(sum(donation), 0) as donations
from ((select expensedate as dte, expense, null as donation
       from expenses
      ) union all
      (select donationdate as dte, null, donation
       from donations
      )
     ) ed
group by date_format(dte, '%Y-%m')
order by yyyymm;