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

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 i1LEFT OUTER JOIN item i2  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)GROUP BY i1.
item_idHAVING COUNT(*) < 4ORDER BY category_id, date_listed;

我假设item表是item_id，这是一个单调递增的伪键。也就是说，在item_id中的较新行对应。item.

下面是它的工作原理：对于每一项，都有一些更新的其他项目。例如，有三个项目比第四个最新项目更新。有零个项目比最新的项目更新。所以我们想比较每个项目(i1)到一组项目(i2)是更新的，并且具有与i1..如果这些新产品的数量少于四个，i1就是其中之一。否则，不要包括它。

这个解决方案的美妙之处在于，不管您有多少类别，它都能工作，如果您更改了类别，它将继续工作。即使某些类别中的项目数少于4个，它也能工作。

---

另一个可行但依赖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 tWHERE 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;

Answer 2

在其他数据库中，可以使用ROW_NUMBER功能。

SELECT
    category_id, image, date_listedFROM(
    SELECT
        category_id, image, date_listed,
        ROW_NUMBER() OVER (PARTITION BY category_id                           
        ORDER BY date_listed DESC) AS rn    FROM item) AS T1WHERE rn <= 4

不幸的是，MySQL不支持ROW_NUMBER函数，但是您可以使用变量来模拟它：

SELECT
    category_id, image, date_listedFROM(
    SELECT
        category_id, image, date_listed,
        @rn := IF(@prev = category_id, @rn + 1, 1) AS rn,
        @prev := category_id    FROM item    JOIN (SELECT @prev := NULL, @rn = 0) AS vars    
        ORDER BY category_id, date_listed DESC) AS T1WHERE rn <= 4

看到它在网上工作：木琴

它的工作如下：

• intially@prev设置为null，@rn设置为0。
• 对于我们看到的每一行，检查类别_id是否与前一行相同。
• 如果是，增加行号。
• 否则，启动一个新类别并将行号重置为1。
• 子查询完成后，最后一步是过滤，以便只保留行号小于或等于4的行。