mysql - 按日期分组时如何检索所有 ID 的最新记录

Question

我需要一个 mysql 查询，它会在按日期分组时为我的每个 ID 获取最新记录，即使该记录与日期无关。我不确定这是否适用于 mysql，但我感谢您提供的任何帮助。

例如，假设我有下表:

user_id | total | timestamp
----------------------------
   1    |   -15 | jan 1
   2    |    -5 | jan 1
   3    |   -10 | jan 1
   1    |    -4 | jan 2
   3    |   -16 | jan 3

我希望它返回下表:

user_id | total | timestamp
----------------------------
   1    |   -15 | jan 1
   2    |    -5 | jan 1
   3    |   -10 | jan 1
   1    |    -4 | jan 2
   2    |    -5 | jan 2
   3    |   -10 | jan 2
   1    |    -4 | jan 3
   2    |    -5 | jan 3
   3    |   -16 | jan 3

如果您需要进一步说明，请告诉我。感谢您的帮助。

Answer 1

SQLFiddle demo

select user_id,

(select total 
        from t 
      where user_id=t2.user_id 
            and t.timestamp <= t2.timestamp 
 order by t.timestamp DESC LIMIT 1) as total,

timestamp
from
(
select user_id,timestamp
from
(select distinct user_id from t) t0
cross join
(select distinct timestamp from t) t1
) t2

order by timestamp,user_id