MySQL 查询分页数据中分组后取每组的前N条记录

在使用数据库查询的时候，如果遇到对分页的数据分组，取每组的前N条，实际就是两次分页，先分页，在对分组的每组排序分页。SQL 如下

select a.* from(select t1.*,(select count(*)+1 from 表 where 分组字段=t1.分组字段 and 排序字段<t1.排序字段) as group_idfrom 表 t1) awhere a.group_id<=3

实例，文章和评论，查询出前2篇文章和对应每篇文章的前2条评论

SELECT a.*,c.content as c_contentFROM (select ax.* from t_article ax ORDER BY id desc limit 0,2) as aLEFT JOIN(select c1.* from ( select t1.*,(select count(1)+1 from t_comment where article_id=t1.article_id and id>t1.id) as group_id from t_comment t1 ) c1where group_id<3order by c1.group_id desc) as c ON a.id = c.article_idWHERE 1 = 1ORDER BY a.id DESC

a – article ,c–comment