MYSQL-为表中的每个类别选择前4条记录 [英] MYSQL - select first 4 records for each category in a table

问题描述

我有一个表INVENTORY，其中除其他列CATEGORY和UPDATED外. 通过这些列对表格进行排序很容易:

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

我想要的是获得一个仅包含每个类别的前4行的结果集. 知道如何完成吗?

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?

推荐答案

与您可以尝试类似

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

这篇关于MYSQL-为表中的每个类别选择前4条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！