MySQL每组排名前2位的记录 [英] MySQL top 2 records per group

问题描述

基本上，考虑到最后的created_datetime，我只需要为每个用户获取最后的2条记录:

Basically I need to get only the last 2 records for each user, considering the last created_datetime:

id | user_id | created_datetime
1  | 34      | '2015-09-10'
2  | 34      | '2015-10-11'
3  | 34      | '2015-05-23'
4  | 34      | '2015-09-13'
5  | 159     | '2015-10-01'
6  | 159     | '2015-10-02'
7  | 159     | '2015-10-03'
8  | 159     | '2015-10-06'

返回(预期输出):

2  | 34      | '2015-10-11'
1  | 34      | '2015-09-10'
7  | 159     | '2015-10-03'
8  | 159     | '2015-10-06'

我正在尝试这个想法:

select user_id, created_datetime, 
$num := if($user_id = user_id, $num + 1, 1) as row_number, 
$id := user_id as dummy 
from logs group by user_id 
having row_number <= 2 

这个想法是只保留前两行，并删除所有其他行.

The idea is keep only these top 2 rows and remove all the others.

有什么想法吗?

推荐答案

您的想法已经完成.我认为这样会更好:

Your idea is close. I think this will work better:

select u.*
from (select user_id, created_datetime, 
             $num := if(@user_id = user_id, @num + 1,
                        if(@user_id := id, 1, 1)
                       ) as row_number
      from logs cross join
           (select @user_id := 0, @num := 0) params
      order by user_id 
     ) u
where row_number <= 2 ;

以下是更改:

• 仅在一个表达式中设置变量. MySQL不保证表达式求值的顺序，因此这很重要.
• 工作在子查询中完成，然后在外部查询中进行处理.
• 子查询使用，而不是group by.
• 外部查询使用而不是having(实际上，在MySQL中having可以工作，但where更合适).

• The variables are set in only one expression. MySQL does not guarantee the order of evaluation of expressions, so this is important.
• The work is done in a subquery, which is then processed in the outer query.
• The subquery uses order by, not group by.
• The outer query uses where instead of having (actually, in MySQL having would work, but where is more appropriate).

这篇关于MySQL每组排名前2位的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！