MySql中取出每个分组中的前N条记录

问题:

mysql中有个表:article(字段:id,type,date),type有1-10,10种类型。现在要用SQL找出每种类型中时间最新的前N个数据组成的集合。

方法1:

[sql]

select a1.* from article a1
inner join
(select a.Category,a.id from article a left join article b
on a.Category=b.Category and a.id<=b.id
group by a.Category,a.id
having count(b.id)<=2
)b1
on a1.Category=b1.Category and a1.id=b1.id
order by a1.Category,a1.id desc

[/sql]

方法2

[sql]
SELECT a.id , a.title , a.cate
FROM article AS a LEFT JOIN article AS b ON a.cate = b.cate AND a.id < b.id
GROUP BY a.id , a.title , a.cate
HAVING COUNT (b .id ) < 5
ORDER BY a.id DESC;
[/sql]

方法3

[sql]
SELECT FROM article AS a
WHERE 5 > ( SELECT COUNT(
) FROM article WHERE cate = a.cate AND id > a.id )
ORDER BY id DESC;
[/sql]

讲解:关键是子查询,这里类似一个while循环,每条记录去匹配和它同类的下一条记录。计算以它开始算起记录条数,意思就是他当前所在的行号,当行号小于5的时候,证明他下面有至多5条记录,则符合子查询条件,返回到结果集里。这样就查询出了每类的前5条记录。