获取每个组的最新n条记录

本文介绍了获取每个组的最新n条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

让我们说一下下表:

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.