获取每个组的最新n条记录 [英] Getting the latest n records for each group

问题描述

让我们说一下下表:

id  coulmn_id  value    date
1      10      'a'     2016-04-01
1      11      'b'     2015-10-02
1      12      'a'     2016-07-03
1      13      'a'     2015-11-11
2      11      'c'     2016-01-10
2      23      'd'     2016-01-11
3      11      'c'     2016-01-09
3      111     'd'     2016-01-11
3      222      'c'     2016-01-10
3      333      'd'     2016-01-11

对于n = 3，我想获取每个ID的最新n条记录<== 3.所以我将得到以下输出:

for n = 3, I want to get the latest n records<=3 for each id. So I will have the following output:

id  column_id  value    date
1      10        'a'     2016-04-01
1      12        'a'     2016-07-03
1      13        'a'     2015-11-11
2      11        'c'     2016-01-10
2      23        'd'     2016-01-11
3      111       'd'     2016-01-11
3      222       'c'     2016-01-10
3      333       'd'     2016-01-11

推荐答案

我正在回答，因为所引用的问题的答案不稳定(我将在此处对此发表评论).

I am answering because the referenced question has an unstable answer (I'll comment on that there).

这是一种可行的解决方案:

Here is a solution that should work:

select t.*
from (select t.*,
             (@rn := if(@id = id, @rn + 1,
                        if(@id := id, 1, 1)
                       )
             ) as seqnum
      from t cross join
           (select @rn := 0, @id := -1) params
      order by id, date desc
     ) t
where seqnum <= 3;

解决方案的区别在于，变量赋值都在单个表达式中. MySQL不保证对表达式求值的顺序，因此，如果代码能够始终如一地工作，这非常重要.

The difference in the solutions is that the variable assignments are all in a single expression. MySQL does not guarantee the order of evaluation of expressions, so this is very important if the code is going to work consistently.

这篇关于获取每个组的最新n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！