mysql 选择前4个,MYSQL-为表中的每个类别选择前4条记录

I have a table INVENTORY that has among other columns CATEGORY and UPDATED.

Sorting the table by these columns is easy:

SELECT * FROM INVENTORY ORDER BY CATEGORY ASC, UPDATED ASC

What I want is to get a result set that contains only the first 4 rows from each category.

Any idea how to get it done?

解决方案

You could try something like

SELECT *

FROM (

SELECT *,

( SELECT COUNT(1)

FROM INVENTORY

WHERE CATEGORY = i.CATEGORY

AND UPDATED < i.UPDATED

) CountTotal

FROM @INVENTORY i

) sub

WHERE sub.CountTotal <= 3