最大日期，在 2 个表 MYSQL 中找到每个用户的余额

Question

我有两个包含用户余额的表。一个是每日表，称为每日表，其中仅显示今天的余额，第二个表合并前几天用户以前的余额。

每日表如下所示：

+---------+-------------------------+----------+----------+----------+
| user_id |   transactions_date     | balance1 | Balance2 | Balance3 |
+---------+-------------------------+----------+----------+----------+
| john    | 2018-05-29 10:10:00.100 |      100 |        0 |        0 |
| mike    | 2018-05-29 09:10:01.300 |      677 |        9 |      100 |
| john    | 2018-05-29 11:05:22.450 |      100 |        2 |       99 |
| philip  | 2018-05-29 10:09:40.200 |        4 |        0 |        1 |
| john    | 2018-05-29 08:21:10.090 |        6 |        0 |        0 |
| mike    | 2018-05-29 12:03:30.200 |      900 |        0 |        1 |
| mike    | 2018-05-29 10:05:00.100 |      188 |        0 |        2 |
| philip  | 2018-05-29 05:24:11.320 |       47 |        0 |        3 |
+---------+-------------------------+----------+----------+----------+

合并表具有相同的结构，如下所示：

+---------+-------------------------+----------+----------+----------+
| user_id |   transactions_date     | balance1 | Balance2 | Balance3 |
+---------+-------------------------+----------+----------+----------+
| john    | 2018-05-24 17:10:00.200 |        9 |       11 |      198 |
| mike    | 2018-04-12 08:11:44.800 |      100 |       13 |       13 |
| philip  | 2018-05-21 12:00:59.320 |       99 |     1000 |      122 |
| jenna   | 2018-05-10 08:12:22.211 |     2000 |        0 |       11 |
| jenna   | 2018-05-11 10:09:10.199 |     2999 |        1 |        1 |
| paul    | 2018-04-01 12:12:11.191 |      888 |      100 |      100 |
+---------+-------------------------+----------+----------+----------+

我的查询需要为每个用户找到它 MAX date_transaction，所以我应该需要一个这样的结果：

+---------+-------------------------+----------+----------+----------+
| user_id |   transactions_date     | balance1 | Balance2 | Balance3 |
+---------+-------------------------+----------+----------+----------+
| john    | 2018-05-29 11:05:22.450 |      100 |        2 |       99 |
| mike    | 2018-05-29 12:03:30.200 |      900 |        0 |        1 |
| philip  | 2018-05-29 10:09:40.200 |        4 |        0 |        1 |
| jenna   | 2018-05-11 10:09:10.199 |     2999 |        1 |        1 |
| paul    | 2018-04-01 12:12:11.191 |      888 |      100 |      100 |
+---------+-------------------------+----------+----------+----------+

我可以通过此查询设法为每日表得出结果，以按用户获取最大日期：

SELECT
t2.user_id ,
t2.balance1 ,
t2.balance2,
t2.balance3
FROM
(
SELECT
user_id ,
MAX(transactions_date) AS max
FROM
daily
GROUP BY
user_id
) t1 ,
(
SELECT
user_id ,
balance1 ,
balance2 ,
balance3 ,
transactions_date
FROM
daily
) t2
WHERE
t1.user_id = t2.user_id
AND t1.max = t2.transactions_date

我不知道是否有更好的方法可以先每天在桌子上完成此过程。然后我试图通过user_id加入每日和合并表来获得最大date_transaction，但没有结果。

我添加了一个 Mysqlfiddle

任何帮助都非常感谢。

Answer 1

SELECT 
A.*
FROM
(SELECT * FROM daily
UNION ALL
SELECT * FROM consolidation) A 
JOIN
(SELECT 
T.user_id, MAX(transactions_date) max_user_trans_date
FROM
(SELECT * FROM daily
UNION ALL
SELECT * FROM consolidation) T
GROUP BY T.user_id) B
ON A.user_id=B.user_id AND A.transactions_date=B.max_user_trans_date; 

看到它在SQL Fiddle上运行。