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

不是很漂亮，但是：

SELECT image 
FROM item 
WHERE date_listed IN (SELECT date_listed 
                      FROM item 
                      ORDER BY date_listed DESC LIMIT 4)

好的，在谷歌搜索后，快速回答是不可能的，至少在MySQL上是不可能的

这个这个线程供参考

也许你应该缓存该查询的结果，如果你害怕使服务器崩溃，并且你希望代码表现得更好

根据类别的恒定程度，以下是最简单的路线

SELECT C.CategoryName, R.Image, R.date_listed
FROM
(
    SELECT CategoryId, Image, date_listed
    FROM 
    (
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Supplies'
      ORDER BY date_listed DESC LIMIT 4
    ) T

    UNION ALL

    SELECT CategoryId, Image, date_listed
    FROM
    (        
      SELECT CategoryId, Image, date_listed
      FROM item
      WHERE Category = 'Pet Food'
      ORDER BY date_listed DESC LIMIT 4
    ) T
) RecentItemImages R
INNER JOIN Categories C ON C.CategoryId = R.CategoryId
ORDER BY C.CategoryName, R.Image, R.date_listed

此解决方案是从另一个 SO 解决方案改编而来的，感谢 RageZ 找到这个相关/类似的问题。

注意

对于 Justin 的用例来说，这个解决方案似乎令人满意。根据您的用例，您可能需要在这篇文章中查看 Bill Karwin 或 David Andres 的解决方案。比尔的解决方案得到了我的投票！看看为什么，因为我把两个查询放在一起;-）

我的解决方案的好处是它每category_id返回一条记录（项目表中的信息是“汇总”的）。我的解决方案的主要缺点是它缺乏可读性，并且随着所需行数的增加而变得越来越复杂（例如每个类别有 6 行而不是 6 行）。此外，随着项目表中行数的增加，它可能会稍微慢一些。（无论如何，如果项目表中符合条件的行数量较少，所有解决方案的性能都会更好，因此建议定期删除或移动旧项目和/或引入标志以帮助 SQL 尽早筛选出行）

第一次尝试（没有用!!）...

这种方法的问题在于，子查询会[理所当然，但对我们不利]产生非常多的行，基于自连接定义的笛卡尔积......

SELECT id, CategoryName(?), tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  here_some_addtional l criteria if needed
ORDER BY id ASC;

第二次尝试。（工作正常！

为子查询添加 WHERE 子句，强制列出的日期分别为 i1、i2、i3 等的最晚、第二最新、最晚等（并且还允许给定类别 ID 的项少于 4 个时出现 null 情况）。此外，还添加了不相关的过滤子句，以防止显示“已售出”的条目或没有图像的条目（增加了要求）

此逻辑假定没有重复的日期列表值（对于给定category_id）。否则，这种情况将创建重复的行。实际上，所列日期的这种用法是 Bill 解决方案中定义/要求的单调递增主键。

SELECT id, CategoryName, tblFourImages.*
FROM category
JOIN (
    SELECT i1.category_id, i1.image as Image1, i2.image AS Image2, i3.image AS Image3, i4.image AS Image4, i4.date_listed
    FROM item AS i1
    LEFT JOIN item AS i2 ON i1.category_id = i2.category_id AND i1.date_listed > i2.date_listed AND i2.sold = FALSE AND i2.image IS NOT NULL
          AND i1.sold = FALSE AND i1.image IS NOT NULL
    LEFT JOIN item AS i3 ON i2.category_id = i3.category_id AND i2.date_listed > i3.date_listed AND i3.sold = FALSE AND i3.image IS NOT NULL
    LEFT JOIN item AS i4 ON i3.category_id = i4.category_id AND i3.date_listed > i4.date_listed AND i4.sold = FALSE AND i4.image IS NOT NULL
    WHERE NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i1.date_listed)
      AND (i2.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i2.date_listed AND date_listed <> i1.date_listed)))
      AND (i3.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i3.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed)))
      AND (i4.image IS NULL OR (NOT EXISTS (SELECT * FROM item WHERE category_id = i1.category_id AND date_listed > i4.date_listed AND date_listed <> i1.date_listed AND date_listed <> i2.date_listed AND date_listed <> i3.date_listed)))
) AS tblFourImages ON tblFourImages.category_id = category.id
--WHERE  --
ORDER BY id ASC;

现在。。。比较以下内容，我引入了一个item_id密钥，并使用 Bill 的解决方案将这些密钥的列表提供给“外部”查询。你可以看到为什么比尔的方法更好......

SELECT id, CategoryName, image, date_listed, item_id
FROM item I
LEFT OUTER JOIN category C ON C.id = I.category_id
WHERE I.item_id IN 
(
SELECT i1.item_id
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id
      AND i1.sold = 'N' AND i2.sold = 'N'
      AND i1.image <> '' AND i2.image <> ''
      )
GROUP BY i1.item_id
HAVING COUNT(*) < 4
)
ORDER BY category_id, item_id DESC

这是每组最大的 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;

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

它是这样工作的：对于每个项目，还有一些其他较新的项目。例如，有三个项目比第四个最新项目更新。没有比最新项目更新的项目。因此，我们要将每个项目 （） 与较新且具有相同类别的项目 （） 集进行比较。如果这些新项目的数量少于四个，则是我们包括的项目之一。否则，不要包含它。i1i2i1i1

此解决方案的优点在于，无论您有多少个类别，它都可以工作，并且如果您更改类别，它将继续工作。即使某些类别中的项目数量少于 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 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;

下面的代码显示了一种在循环中执行此操作的方法 它肯定需要大量的编辑，但我希望它有所帮助。

        declare @RowId int
 declare @CategoryId int
        declare @CategoryName varchar(MAX)

 create table PART (RowId int, CategoryId int, CategoryName varchar)
 create table  NEWESTFOUR(RowId int, CategoryId int, CategoryName varchar, Image image)
        select RowId = ROW_NUMBER(),CategoryId,CategoryName into PART from [Category Table]


        set @PartId = 0
 set @CategoryId = 0 
 while @Part_Id <= --count
 begin
   set @PartId = @PartId + 1
          SELECT @CategoryId = category_id, @CategoryName = category_name from PART where PartId = @Part_Id
          SELECT RowId = @PartId, image,CategoryId = @category_id, CategoryName = @category_name   FROM item into NEWESTFOUR where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

 end
 select * from NEWESTFOUR
 drop table NEWESTFOUR
        drop table PART

在其他数据库中，您可以使用该函数执行此操作。ROW_NUMBER

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

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

SELECT
    category_id, image, date_listed
FROM
(
    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 T1
WHERE rn <= 4

查看它在线工作：sqlfiddle

其工作原理如下：

• Intially @prev设置为 NULL，@rn设置为 0。
• 对于我们看到的每一行，检查category_id是否与上一行相同。
  • 如果是，则递增行号。
  • 否则，请启动一个新类别并将行号重置回 1。
• 子查询完成后，最后一步是进行筛选，以便仅保留行号小于或等于 4 的行。

最近我遇到了类似的情况，我尝试了一个对我有用的查询，它独立于数据库

SELECT i.* FROM Item AS i JOIN Category c ON i.category_id=c.id WHERE
(SELECT count(*) FROM Item i1 WHERE 
i1.category_id=i.category_id AND 
i1.date_listed>=i.date_listed) <=3 
ORDER BY category_id,date_listed DESC;

这相当于运行 2 个 for 循环并检查比这更新的项目是否小于 3