我有两个包含用户余额的表。一个是daily表，称为daily，其中只显示今天的余额，另一个是合并前几天用户以前的余额。
daily表类似于：

+---------+-------------------------+----------+----------+----------+
| 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 |
+---------+-------------------------+----------+----------+----------+

我的查询需要为每个用户查找it max date\u事务，因此我需要如下结果：

+---------+-------------------------+----------+----------+----------+
| 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 |
+---------+-------------------------+----------+----------+----------+

我可以通过对daily表的查询来获得用户的最大日期：

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

我不知道是否有更好的方法来做这一进程的表每天第一。然后我尝试通过用户id加入daily和consolidation表来获取max date\事务，但没有结果。
我加了一把小提琴
非常感谢您的帮助。