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

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

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

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

SELECT
 a.*
,c.content as c_content

FROM
 (select ax.* from t_article ax ORDER BY id desc limit 0,2) as a
LEFT 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
 ) c1
where group_id<3
order by c1.group_id desc
) as c ON a.id = c.article_id
WHERE
1 = 1
ORDER BY
 a.id DESC

a – article ,c–comment