mysql为所有同名的人选择最新的日期 [英] mysql select latest by date for all with same name

问题描述

我有一个用户积分表:

id, created_at, user_id, points
1, 2018-11-01, 1, 20
2, 2018-11-03, 1, 12
3, 2018-11-04, 2, 15
...

我现在想选择前 50 个用户，但只考虑用户的最新点数.即用户 1 有两个条目，只有最新的一个 (2018-11-03) 应该计算在内.

I now want to select the top 50 users, but only considering the users latest points. i.e. user 1 has two entries and only the latest one (2018-11-03) should count.

预期结果:

id, created_at, user_id, points
3, 2018-11-04, 2, 15
2, 2018-11-03, 1, 12
...

推荐答案

在 Derived Table，你可以为每个user_id获取created_at(最新的created_at)的最大值.然后，您可以将此结果集加入主表，以仅获取与最新 created_at 对应的行.

In a Derived Table, you can the maximum values of created_at (latest created_at) for every user_id. You can then join this result-set to the main table, to get only the row corresponding to latest created_at.

现在，使用此结果集按降序对 points 进行排序，并使用 LIMIT 50

Now, use this result-set to sort on the points in Descending order and consider Top 50 only, using LIMIT 50

SELECT t.*
FROM your_table AS t
JOIN
( SELECT
    user_id, MAX(created_at) AS latest_created_at 
  FROM your_table
  GROUP BY user_id 
) AS dt 
  ON dt.user_id = t.user_id AND 
     dt.latest_created_at = t.created_at 
ORDER by t.points DESC
LIMIT 50

这篇关于mysql为所有同名的人选择最新的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！