我搜索了一段时间，但没有找到任何解决问题的方法。。。
我有两张表：收入，支出
我有列：
- earnings.amount
- earnings.date
- 费用
- 费用。日期
我想把我所有收入的总和按年度和月份显示出来，所以我这样做了，效果很好。。。
SELECT
DATE_FORMAT(date, '%Y%m') AS 'date',
SUM(amount) as 'total_earnings'
FROM earnings
GROUP BY DATE_FORMAT(date, '%Y%m')
输出与此类似。。。
date,total_earnings
201901,5000
201902,5500
201903,6200
etc.
我在费用表上也这样做，一切都很好。
我的问题：当我试图编写一个查询，显示按年度和月份分组的总收入和总费用时，我会得到一个显示huuuge数字的笛卡尔结果。
这是一个显示笛卡尔结果的示例查询：
SELECT
DATE_FORMAT(ea.date, '%Y%m') AS date,
SUM(ea.amount) AS 'total_earnings',
SUM(ex.amount) AS 'total_expenses',
FROM earnings ea
INNER JOIN expenses ex
ON DATE_FORMAT(ea.date, '%Y%m') = DATE_FORMAT(ex.date, '%Y%m')
GROUP BY DATE_FORMAT(ex.date, '%Y%m')
如何在单个查询中显示每月收入和每月支出的总额？
所需输出：
date,total_earnings,total_expenses
201901,5000,1000
201902,5500,1100
201903,6200,1250
提前感谢！
首先需要聚合，然后是JOIN
SELECT ea.date, total_earnings, total_expenses
FROM (
SELECT DATE_FORMAT(date, '%Y%m') AS date
, SUM(amount) AS total_earnings
FROM earnings
GROUP BY DATE_FORMAT(date, '%Y%m')
) ea
JOIN (
SELECT DATE_FORMAT(date, '%Y%m') AS date
, SUM(amount) AS total_expenses
FROM expenses
GROUP BY DATE_FORMAT(date, '%Y%m')
) ex ON ex.date = ea.date
也许您不需要联接。。
drop table if exists t;
create table t
(expense_amount int,expense_date date, earnings_amount int, earnings_date date);
insert into t values
(10,'2020-01-01',null,null),
(10,'2020-01-01',null,null),
(10,'2020-02-01',null,null),
(null,null,10,'2020-01-01'),
(10,'2020-03-01',10,'2020-03-01');
select coalesce(expense_date,earnings_date) date,
sum(case when expense_amount is not null then expense_amount else 0 end) expense,
sum(case when earnings_amount is not null then earnings_amount else 0 end) earnings
from t
group by coalesce(expense_date,earnings_date);
+------------+---------+----------+
| date | expense | earnings |
+------------+---------+----------+
| 2020-01-01 | 20 | 10 |
| 2020-02-01 | 10 | 0 |
| 2020-03-01 | 10 | 10 |
+------------+---------+----------+
3 rows in set (0.001 sec)
如果你的数据模型不同，1行可以包含不同日期的支出和收入，那么这对你来说不起作用，你应该单独生成总额并将其相加。
您可以使用子查询实现这一点：
SELECT `date`, SUM(earnings) AS total_earnings, SUM(expenses) AS total_expenses
FROM (
SELECT DATE_FORMAT(earnings.date, '%Y%m') AS `date`, `amount` AS earnings, 0 AS expenses
FROM earnings
UNION
SELECT DATE_FORMAT(expenses.date, '%Y%m') AS `date`, 0 AS earnings, `amount` AS expenses
FROM expenses
) expenses_earnings
GROUP BY `date`
编辑：
使用YEAR(date)和MONTH(date)而不是DATE_FORMAT，这将允许您使用汇总计算每年的总额和总总额。
SELECT `year`, `month`, SUM(earnings) AS total_earnings, SUM(expenses) AS total_expenses
FROM (
SELECT YEAR(earnings.date) AS `year`, MONTH(earnings.date) AS `month`, `amount` AS earnings, 0 AS expenses
FROM earnings
UNION
SELECT YEAR(expenses.date) AS `year`, MONTH(expenses.date) AS `month`, 0 AS earnings, `amount` AS expenses
FROM expenses
) expenses_earnings
GROUP BY `year`, `month`