mysql 中按照组来查询_mysql - MYSQL在子查询中按组，但需要所有数据 - SO中文参考 - www.soinside.com...

我有两个表，一个是交易，另一个是支出

交易记录表：id amount created

1 300 2019-10-01 00:00:00

2 200 2019-11-01 00:00:00

3 230 2019-11-13 00:00:00

4 130 2019-11-13 00:00:00

费用表：id amount created

1 600 2019-11-13 00:00:00

两个表的总和，我在下面写了对我来说很好的查询。(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , sum(t1.amount) as ReceiveAmount,ex.amount as ExpensesAmount

FROM transactions as t1

LEFT JOIN (

SELECT sum(e.amount) as amount, created

FROM expenses as e

group by date(e.created)

) as ex

ON date(ex.created) = date(t1.created)

GROUP BY date(t1.created))

UNION

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date, sum(t1.amount) as Receive,ex.amount as ExpensesAmount

FROM transactions as t1

RIGHT JOIN (

SELECT sum(e.amount) as amount, created

FROM expenses as e

group by date(e.created)

) as ex

ON date(t1.created) = date(ex.created)

GROUP BY date(t1.created)) ORDER BY Date

输出：Date ReceiveAmount ExpensesAmount

2019-10-01 300 NULL

2019-11-01 200 NULL

2019-11-13 360 600

但是现在，如果我想从两个表中获取所有金额而不求和。我缺少日期2019-11-13的一笔交易。(SELECT IFNULL(date(t1.created), date(ex.created)) as Date , t1.amount as ReceiveAmount,ex.amount as ExpensesAmount

FROM transactions as t1

LEFT JOIN (

SELECT e.amount as amount, created

FROM expenses as e

group by date(e.created)

) as ex

ON date(ex.created) = date(t1.created)

GROUP BY date(t1.created))

UNION

(SELECT IFNULL(date(t1.created), date(ex.created)) as Date, t1.amount as Receive,ex.amount as ExpensesAmount

FROM transactions as t1

RIGHT JOIN (

SELECT e.amount as amount, created

FROM expenses as e

group by date(e.created)

) as ex

ON date(t1.created) = date(ex.created)

GROUP BY date(t1.created)) ORDER BY Date

输出：2019-10-01 300 NULL

2019-11-01 200 NULL

2019-11-13 230 600

2019-11-13日期缺少一笔交易。

预期结果：2019-10-01 300 NULL

2019-11-01 200 NULL

2019-11-13 230 600

2019-11-13 130 NULL

[这里，我怎么能得到我想要的结果？