mysql多个分类取n条_MySQL获取所有分类和每个分类的前N条记录

比如有文章表 Article(Id,Category,InsertDate)，现在要用SQL找出每种类型中时间最新的前N个数据组成的集合，一段不错的代码，留存备用，转自(loveyuki.com)，感谢。

SELECT A1.*

FROM Article AS A1

INNER JOIN (SELECT A.Category,A.InsertDate

FROM Article AS A

LEFT JOIN Article AS B

ON A.Category = B.Category

AND A.InsertDate <= B.InsertDate

GROUP BY A.Category,A.InsertDate

HAVING COUNT(B.InsertDate) <= @N

) AS B1

ON A1.Category = B1.Category

AND A1.InsertDate = B1.InsertDate

ORDER BY A1.Category,A1.InsertDate DESC

@N 就是你要取多少条

下面是我用到了一个产品分类表中，superId是大分类，prcid是产品分类。能用SQL完成的功能就要尽量用SQL语句来完成，这既简洁又高效。

SELECT

A1.*

FROM

prcKx AS A1

INNER JOIN (

SELECT

A.superId,

A.prcid

FROM

prcKx AS A

LEFT JOIN prcKx AS B ON A.superId = B.superId

AND A.prcid <= B.prcid

GROUP BY

A.superId,

A.prcid

HAVING

COUNT(B.prcid) <= 7

) AS B1 ON A1.superId = B1.superId

AND A1.prcid = B1.prcid

ORDER BY

superId,

prcid