gpt4 book ai didi

Mysql - 将具有不同日期值的两个表与分组依据组合

转载 作者:行者123 更新时间:2023-11-28 23:21:53 27 4
gpt4 key购买 nike

我正在做项目,我需要在哪里显示用户每天赚取的积分和交换的积分。

我有两个不同的表来存储数据。 tables(a,b) 如下:表a:

id   user_id     earned   created_at
--------------------------------------------
1 1 1 2016-12-14
2 2 2 2016-12-14
3 2 1 2016-12-14
4 3 1 2016-12-15

表b:

id   user_id   exchanged   created_at
--------------------------------------------
1 1 1 2016-12-14
2 1 2 2016-12-14
3 2 1 2016-12-14
4 4 1 2016-12-15
5 3 3 2016-12-16

我想按如下日期合并两个表

user_id     earned  exchanged    created_at
-------------------------------------------------
1 1 1 2016-12-14
2 3 1 2016-12-14
3 1 0 2016-12-15
4 0 1 2016-12-15
3 0 3 2016-12-16

我已经尝试搜索 SO,我最终得到以下查询 ( sqlfiddle ):

select user_id, created_at, sum(earned) as earned, sum(exchanged) as exchanged from (
SELECT
a.user_id,
DATE_FORMAT(a.created_at, '%d-%m-%Y') AS created_at,
a.earned,
0 AS exchanged
FROM
a
LEFT JOIN
b ON DATE_FORMAT(a.created_at, '%y%m%d') = DATE_FORMAT(b.created_at, '%y%m%d')
UNION SELECT
b.user_id,
DATE_FORMAT(b.created_at, '%d-%m-%Y') AS created_at,
0 AS earned,
b.exchanged
FROM
a
RIGHT JOIN
b ON DATE_FORMAT(a.created_at, '%y%m%d') = DATE_FORMAT(b.created_at, '%y%m%d')
) as tbl group by tbl.created_at, tbl.user_id

但它显示的兑换点数不正确。

最佳答案

尝试...

select user_id, created_at, sum(earned), sum(exchanged) from
((select id, user_id, earned, 0 as exchanged, created_at from a)
union all
(select id, user_id, 0 as earned, exchanged, created_at from b)) combined
group by user_id, created_at

关于Mysql - 将具有不同日期值的两个表与分组依据组合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41179037/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com