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

Question

我有两个包含用户余额的表。一个是名为 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 |
+---------+-------------------------+----------+----------+----------+

我的查询需要为每个用户找到 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

我不知道是否有更好的方法可以每天先在餐 table 上做这个过程。然后尝试通过user_id加入daily和consolidation表获取max 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; 

查看run on SQL Fiddle .