如何只为每个用户选择最新行? [英] How to select only the latest rows for each user?

问题描述

我的桌子看起来像这样:

My table looks like this:

id  | user_id | period_id | completed_on
----------------------------------------
1   | 1       | 1         | 2010-01-01
2   | 2       | 1         | 2010-01-10
3   | 3       | 1         | 2010-01-13
4   | 1       | 2         | 2011-01-01
5   | 2       | 2         | 2011-01-03
6   | 2       | 3         | 2012-01-13
... | ...     | ...       | ...

我要只选择最新的用户期间条目，请记住，用户不会都具有相同的期间条目.

I want to select only the latest users periods entries, bearing in mind that users will not all have the same period entries.

基本上(假设我所拥有的就是上面的表格)，我想要得到这个:

Essentially (assuming all I have is the above table) I want to get this:

id  | user_id | period_id | completed_on
----------------------------------------
3   | 3       | 1         | 2010-01-13
4   | 1       | 2         | 2011-01-01
6   | 2       | 3         | 2012-01-13

以下两个查询总是导致选择了第一个user_id出现，而不是最新的(因为排序是在从我所理解的行中选择之后发生的):

Both of the below queries always resulted with the first user_id occurance being selected, not the latest (because the ordering happens after the rows are selected from what I understand):

SELECT
    DISTINCT user_id,
    period_id,
    completed_on
FROM my_table
ORDER BY
    user_id ASC,
    period_id DESC

SELECT *
FROM my_table
GROUP BY user_id
ORDER BY
    user_id ASC,
    period_id DESC

推荐答案

似乎可以使用MAX和子查询来工作:

Seems like this should work using MAX and a subquery:

SELECT t.Id, t.User_Id, t.Period_Id, t.Completed_On
FROM my_table t
   JOIN (SELECT Max(completed_on) Max_Completed_On, t.User_Id
         FROM my_table
         GROUP BY t.User_ID
         ) t2 ON
      t.User_Id = t2.User_Id AND t.Completed_On = t2.Max_Completed_On

但是，如果您可能有多条记录，其中每位用户的completed_on日期相同，则可能会返回多条记录.根据您的需求，可能会在子查询中添加一个MAX(Id)并加入该行.

However, if you potentially have multiple records where the completed_on date is the same per user, then this could return multiple records. Depending on your needs, potentially adding a MAX(Id) in your subquery and joining on that would work.

这篇关于如何只为每个用户选择最新行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！