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

Question

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

例如:

宠物用品

img1
img2
img3
img4

宠物食品

img1
img2
img3
img4

我知道我可以通过像这样查询每个类别的数据库来轻松解决这个问题:

SELECT id FROM category

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

SELECT image FROM item where category_id = :category_id ORDER BY date_listed DESC LIMIT 4

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

有人知道这是否可能吗？或者，如果 33 次通话没什么大不了的，我应该用简单的方法来做。

Answer 1

这是每组最大 n 问题，也是一个非常常见的 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) 与较新且与 i1 具有相同类别的项目集 (i2) 进行比较。如果这些较新项目的数量少于四个，则 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;