mysql - MYSQL 在子查询中分组但需要所有数据
问题描述
我有两张桌子,一张是交易,另一张是费用
交易表:
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
但是现在如果我想从两个表中获取所有金额而不求和。我错过了 date 的一笔交易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
这里 date 缺少一笔交易2019-11-13
。
预期结果 :
2019-10-01 300 NULL
2019-11-01 200 NULL
2019-11-13 230 600
2019-11-13 130 NULL
在这里,我怎样才能得到我想要的结果?
解决方案
如果您正在运行 MySQL 8.0,您可以将row_number()
s 分配给来自每个带有created
分区的表的记录,然后创建left join
:
select t.created, t.amount, e.amount
from (
select
t.*,
row_number() over(partition by created order by id) rn
from transactions t
) t
left join (
select
e.*,
row_number() over(partition by created order by id) rn
from expenses e
) e
on e.created = t.created and e.rn = t.rn
如果两个表中的任何一个可能具有另一个表中不存在的日期,那么它有点复杂。基本上我们需要 emulate full join
,这在 MySQL 中是不存在的。这是一种方法union all
:
select created, max(t_amount) t_amount, max(e_amount) e_amount
from (
select
created,
amount t_amount,
null e_amount,
row_number() over(partition by created order by id) rn
from transactions
union all
select
created,
null,
amount,
row_number() over(partition by created order by id)
from expenses
) d
group by created, rn
推荐阅读
- mysql - MySQL 连接到 Google Cloud SQL 实例语法错误
- haskell - Haskell 如何编译大数?
- multithreading - Spring Boot:我们如何实现多个@Scheduled 任务,每个任务都有自己的线程池?
- buffalo - show can set disabled pop 错误:mysql select one: sql: no rows in result set
- react-native - 如何在 react-native 中根据 useSelector 挂钩的值操作视图
- concurrency - 判断高并发的原因
- powerquery - 在 PowerQuery 中使用发布
- javascript - RangeError:超过最大调用堆栈大小 Javascript 问题
- java - 满足条件的元素的Java迭代器
- php - apache在发布请求时返回403错误,输入变量计数保持不变,值发生变化