如何选择每个类别中最新的四个项目？

Question

我有一个项目数据库。每个项目都按类别表中的类别ID进行分类。我正在尝试创建一个列出每个类别的页面，并在每个类别下方显示该类别中的4个最新项目。

例如：

宠物用品

img1
img2
img3
img4

宠物食品

img1
img2
img3
img4

我知道我可以通过查询数据库来轻松解决此问题，如下所示：

选择类别的ID

然后遍历该数据并查询数据库中的每个类别以获取最新的项目：

从项目中选择图像，其中category_id =：category_id 
ORDER BY date_listed DESC LIMIT 4

我要弄清楚的是，我是否可以仅使用1个查询并获取所有这些数据。我有33个类别，因此我认为这可能有助于减少对数据库的调用次数。

有人知道这是否可能吗？或者，如果33个电话没什么大不了的，那么我应该以简单的方式进行。

Answer 1

这是每组最大的问题，也是一个非常常见的SQL问题。

这是我通过外部联接解决的方法：

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4
ORDER BY category_id, date_listed;

我假设item表的主键是item_id，并且它是单调递增的伪键。也就是说，中的较大值item_id对应于中的新行item。

它是这样工作的：对于每个项目，还有一些其他较新的项目。例如，有三个项目比第四个最新项目新。有零个项目比最新的项目新。因此，我们想将每个项目（i1）与i2较新的项目集（）进行比较，并具有与相同的类别i1。如果这些较新的项目的数量少于四个，i1则是我们包括的项目之一。否则，请勿将其包括在内。

该解决方案的优点在于，无论您有多少个类别，它都可以工作，并且如果您更改类别，它可以继续工作。即使某些类别中的项目数少于四个，它也可以工作。

另一个可行但依赖于MySQL用户变量功能的解决方案：

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (@g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 3;

MySQL 8.0.3引入了对SQL标准窗口函数的支持。现在，我们可以像其他RDBMS一样解决此类问题：

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;