mysql - 如何获取mysql中每个组的前300行？

Question

我有这样的 table

dlrecipientid  dlrecipientlistid
1                  1
2                  1
..                 ..
999                1
1000               1
1001               2
1002               2
..                 ..
1999               2
2000               2

如何查询并获取前300行dlrecipientlistid

示例

dlrecipientid  dlrecipientlistid
1                  1
2                  1
..                 ..
299                1
300                1
1001               2
1002               2
..                 ..
1299               2
1300               2

我需要在mysql中查询

Answer 1

您可以通过以下方式进行自加入:

  SELECT tbl1.* FROM your_table AS tbl1
  LEFT OUTER JOIN your_table AS tbl2 
    ON (tbl1.dlrecipientlistid= tbl2.dlrecipientlistid 
        AND tbl1.dlrecipientid > tbl2.dlrecipientid)
  GROUP BY tbl1.dlrecipientid  
  HAVING COUNT(*) < 300
  ORDER BY tbl1.dlrecipientid,tbl1.dlrecipientlistid DESC;

我已经更新了查询并在 SQLFIDDLE 中进行了充分测试