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

我有一个物品数据库。每个项目都按类别表中的类别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个电话没什么大不了的，那么我应该以一种简单的方式来做到。

您的类别有多“静态”？是不时更改的列表还是恒定的？

类别非常静态（很少会改变）。除非我添加一个我认为不会发生或非常罕见的类别，否则它们永远不会真正改变

@justinl：如果它们是静态的，最好使用简单的UNION语句。请参阅我的答案作为示例。

@justinl建议的标题标题为：“ MySql，A JOIN B：对于A中的每个PK，如何限制B中的N行？”

#1 楼

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

这是我通过外部联接解决的方法：
我假设item表的主键是item_id，并且它是一个单调递增的伪密钥。也就是说，item_id中的较大值对应于item中的较新行。

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

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

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

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;

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

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;

仅供参考：如果要约束其他表列，则必须在ON括号中，并在GROUP BY上方使用WHERE，例如：ON（i2.active = TRUE）WHERE i1.active = TRUE

–justinl
09年9月29日在5:17

@drake，您对此表示正确。但是，要找到每个组中排名靠前的1个，还有另一种效率更高的查询样式，因为它可以完全不使用GROUP BY来完成任务。参见例如我在stackoverflow.com/questions/121387/…中的回答

– Bill Karwin
15年7月8日在14:55

@drake，以我的经验，任何区别都很小。您可以自己确定基准。通常，出于逻辑原因，应该使用COUNT（column）-当您希望计数跳过列为NULL的行时。 COUNT（*）计算所有行，无论该列是否为空。

– Bill Karwin
15年7月8日在22:41

@Davos：dev.mysql.com/doc/refman/8.0/en/…

– Bill Karwin
18年1月15日在16:43

@RaymondNijland，是的，MySQL的AUTO_INCREMENT是单调递增的伪密钥。其他SQL实现使用诸如SEQUENCE，IDENTITY等术语。

– Bill Karwin
19年5月14日在15:25

#2 楼

此解决方案是另一个SO解决方案的改编，感谢RageZ定位此相关/相似问题。

注意

该解决方案对于Justin的用例而言似乎令人满意。根据您的用例，您可能需要在此帖子中查看Bill Karwin或David Andres的解决方案。比尔的解决方案获得我的投票！看看为什么，当我将两个查询都放在一起时;-)

我的解决方案的好处是，它为每个category_id返回一条记录（项目表中的信息为“汇总”）。我的解决方案的主要缺点是缺乏可读性，并且随着所需行数的增加（例如，每个类别有6行而不是6行）而变得越来越复杂。另外，随着项目表中的行数增加，它可能会稍微慢一些。（无论如何，所有解决方案在item表中的合格行数较少的情况下都将表现更好，因此建议定期删除或移动较旧的item和/或引入标志以帮助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个项目的情况为空）。还添加了不相关的过滤器子句，以防止显示“已售出”条目或没有图像的条目（附加要求）

此逻辑假定没有重复的日期列出值（对于给定的category_id）。否则，此类情况将创建重复的行。有效地使用列出的日期是比尔的解决方案中定义/要求的单调递增主键。

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的解决方案将这些列表提供给“外部”查询。您会明白为什么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

现在我得到：＃1054-'order子句'中的未知列'date_listed'如果我从ORDER子句中删除了date_listed，它确实起作用，但是它似乎不会遍历不同的类别，而是只列出相同的类别一遍又一遍

–justinl
09年9月18日在5:46

好的，我弄清楚了date_listed（就像我们对category_id所做的那样，我只是将它添加到了JOIN的子查询中）。但是返回结果的每一行都显示相同的categoryName，ID和图像路径

–justinl
09年9月18日在5:50

哈哈，太近了。但是返回的行都来自同一类别（即使我有六个项目属于不同类别）。

–justinl
09年9月18日在6:20

实际上，我感觉很不好，我让您步入正轨，但是设计存在缺陷。基本上，子查询从自连接表示的笛卡尔积中产生了[理所当然但对我们不利]整行的数据。解决此问题后我们可以解决的另一个附带问题是，如现在所写，图像表中不能有两个记录具有相同的日期和相同的category_id ...

–mjv
09年9月18日在6:54

不用担心我的时间。这有点像挑战，加上不错的自我检查，当“简单”的东西最终在我的脸上浮现时...我会再等30分钟...

–mjv
09年9月18日在7:05

#3 楼

在其他数据库中，您可以使用ROW_NUMBER函数来执行此操作。不幸的是，MySQL不支持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

在线查看它：sqlfiddle

它的工作原理如下：

@prev通常设置为为NULL，并且@rn设置为0。
对于我们看到的每一行，请检查category_id是否与上一行相同。

如果是，则增加行号。 />否则，请启动一个新类别，然后将行号重置为1。保留4个。

幸运的是，MySQL 8.0将支持窗口功能

–卢卡斯·索佐达（Lukasz Szozda）
17年11月30日在20:01

#4 楼

不是很漂亮，但是：

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

每个类别都需要调用它，对吗？有没有一种方法可以将全部归为1个查询？

–justinl
09年9月18日在4:09

糟糕，不知道您无法在子查询中执行LIMIT

–tster
09-09-18 at 4:11

另一个问题是：多个图像可能具有相同的date_list，并且最终可能会得到不正确的数据

–史蒂夫·麦克劳德（Steve McLeod）
09年9月18日在8:47

您可以在子查询中进行限制，它只能是1个限制。

–Jage
2010-3-19在18:56

#5 楼

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

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

谢谢大卫。那么，将所有查询合并为1个大查询的方法是否比进行33个单独的查询（每个类别1个查询）更有效？

–justinl
09年9月18日在4:20

是的，如果仅仅是因为您可能正在将33个单独的查询作为来自数据库的单独请求，就可以。这些时间中的一部分只是简单地往返于数据库服务器之间来回穿梭数据。我还已将UNION修改为UNION ALL，它不会检查并删除重复项。您可能在任何情况下都不会有任何东西。

–大卫·安德烈斯（David Andres）
09年9月18日在4:23

谢谢。您是正确的，我不会有任何重复，因为所有项目都具有PK。而且似乎我可以通过查询所有类别ID来构建查询，然后通过遍历这些结果并将其组合为字符串并将该字符串用作新查询来构建查询。

–justinl
09年9月18日在4:26

如果那是您想要的。我说为什么要打扰，尤其是当您告诉我类别更改不经常发生时。如果是这种情况，请复制并粘贴。当类别更改时，您可以返回此查询并进行适当的修改。它不会是自动的，但是会起作用。

–大卫·安德烈斯（David Andres）
09年9月18日在4:28

我只是意识到我在您的查询中不了解如何加入类别。例如。这些SELECT语句如何知道什么是类别？因为类别ID和名称在另一个表中。

–justinl
09年9月18日在4:28

#6 楼

下面的代码显示了一种循环执行的方法
它确实需要进行大量编辑，但我希望它能有所帮助。

        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

#7 楼

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

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进行循环并检查是否有项目比这新的少于3

#8 楼

谷歌搜索快速答案之后，确定是不可能的，至少在mysql

上，此线程仅供参考

如果您害怕这样做，应该缓存该查询的结果使服务器崩溃，您希望代码执行得更好

编程黑洞网