mysql - 选择每个 ID 的前 3 次出现

Question

我有下表

ID | date   
1   | 2016-01-01   
1   | 2016-01-02   
1   | 2016-02-01   
1   | 2016-03-01  
1   | 2016-03-11   
2   | 2016-01-01   
2   | 2016-01-03   
2   | 2016-01-05   
2   | 2016-02-01   
2   | 2016-03-01   
3   | 2016-01-03   
3   | 2016-01-04   
3   | 2016-01-05   
3   | 2016-01-06 

我需要查询返回每个 ID 的前 3 个日期，结果应该是:

ID | date   
1   | 2016-01-01   
1   | 2016-01-02   
1   | 2016-02-01   
2   | 2016-01-01   
2   | 2016-01-03   
2   | 2016-01-05   
3   | 2016-01-03   
3   | 2016-01-04   
3   | 2016-01-05 

Answer 1

你可以为此使用变量:

SELECT ID, `date`
FROM (
  SELECT ID, `date`,
         @rn := IF(@id = ID, @rn + 1,
                   IF(@id := ID, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @id := 0) AS vars
  ORDER BY ID, `date`) AS t
WHERE t.rn <= 3