logo标签列表

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

sqlmysqlgreatest-n-per-group
39
我有一个物品数据库。每个物品都用来自类别表的类别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

我正在尝试弄清楚是否可以只使用一个查询来获取所有那些数据。我有33个类别，所以也许这样做有助于减少对数据库的调用次数。

有人知道这是否可行吗？还是说33个调用不算什么，我应该采用简单的方法？

你的分类有多“静态”？它是一个不时更改的列表还是恒定不变的？ - David Andres
这些类别非常静态（很少会改变）。 除非我添加一个类别，但我认为这不太可能发生或者极其罕见。 - justinl
@justinl：如果它们是静态的，最好使用简单的UNION语句。请参考我的答案示例。 - David Andres
@justinl 建议的问题标题为：“MySql，A JOIN B：如何限制每个A的PK从B选择N行？” - mjv
你可以在这里了解窗口功能 https://dev59.com/B2sz5IYBdhLWcg3wpZvy#38854846，并使用它。 - Paramvir Singh Karwal
8个回答
98
这是最大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的值越大，对应的行就越新。

它的工作原理如下：对于每个物品，有一些其他物品比它更新。例如，比第四个最新的物品更新的物品有三个。没有物品比最新的物品更新。因此，我们要将每个物品(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;
1
请注意：如果您想对其他表列进行约束，则必须在ON括号中执行此操作，并在GROUP BY上方使用WHERE语句。 例如：ON（i2.active = TRUE） WHERE i1.active = TRUE - justinl
1
@drake，你说得没错。但是对于查找每个组的前1个，还有另一种查询方式更有效，因为它可以在不使用GROUP BY的情况下完成任务。例如，请参见我的答案https://dev59.com/VHVD5IYBdhLWcg3wAWkO - Bill Karwin
1
@drake，根据我的经验，它们之间的差异非常微小。你可以自己进行基准测试以确保。一般来说，你应该使用COUNT(column)的逻辑原因是当你想要计算跳过列为空的行数时。而COUNT(*)会计算所有行，无论列是否为空。 - Bill Karwin
1
@Davos：https://dev.mysql.com/doc/refman/8.0/en/faqs-general.html#faq-mysql-why-8.0 - Bill Karwin
1
@RaymondNijland，是的，MySQL的AUTO_INCREMENT是一个单调递增的伪键。其他SQL实现使用诸如SEQUENCE、IDENTITY等术语。 - Bill Karwin
显示剩余15条评论
5

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

注意

对于Justin的用例，这个解决方案似乎是令人满意的。根据您的用例，您可能想要检查Bill Karwin或David Andres在此帖子中的解决方案。我投票给了Bill的解决方案！看看为什么，因为我将两个查询并排放置；-）

我的解决方案的好处是它返回每个category_id的一个记录（item表中的信息被“卷起”）。我的解决方案的主要缺点是其可读性不足，并且随着所需行数的增加而变得越来越复杂（例如，每个类别需要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）。否则，这种情况将创建重复的行。实际上，这种使用日期列表的效果就是按照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的解决方案提供这些键的列表给“外部”查询。你可以看到为什么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
好的，我已经解决了date_listed（我只是像我们在category_id中所做的那样将其添加到JOIN的子查询中）。但是返回结果的每一行都显示相同的categoryName、ID和图像路径。 - justinl
哈哈，它非常接近。但返回的行都来自同一类别（即使我有半打不同类别的项目）。 - justinl
我还应该注意到，我将在每个项目上检查另外两个参数。首先是每个项目都有一个布尔类型的sold列。如果已售出，则不想显示该项目。此外，我不希望返回没有图像的项目。WHERE子句类似于WHERE sold = 0 AND image <> ''。 - justinl
@justin 感谢您的友好接受。在这种情况下，奖励的是努力而不是智商;-) 我编辑了我的帖子，以便将来的读者远离我不太优雅的解决方案。除非有人希望使用滚动逻辑并确信每个组仅需要前3或4个项目，否则没有必要自我施加这种滥用。尽管如此，这很有趣，我希望我没有浪费太多_您的_时间。 - mjv
@justin 请删除一些你在这里的评论，以便清理/澄清。 - mjv
显示剩余9条评论
3

在其他数据库中，您可以使用 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

在线演示请查看: SQL Fiddle

它的工作原理如下:

  • 最初@prev被设置为NULL，@rn被设置为0。
  • 对于我们所见到的每一行，检查category_id是否与前一行相同。
    • 如果是，则增加行号。
    • 否则，开始一个新的类别并将行号重置为1。
  • 当子查询完成时，最后一步是过滤只保留行号小于或等于4的行。
幸运的是，MySQL 8.0将支持窗口函数。 - Lukasz Szozda
0

最近我遇到了类似的情况，我尝试了一个对我有效的查询，它不依赖于数据库。

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;

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

0

根据您的类别有多稳定，以下是最简单的路线

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
谢谢David。那么将所有查询组合成一个大查询的方式是否比为每个类别执行33个单独的查询（每个类别1个）更有效？ - justinl
如果您想这样做，我为什么要费心呢？尤其是如果您告诉我类别更改并不经常发生。如果是这种情况，那就复制粘贴吧。当类别更改时，您可以回到此查询并进行相应的修改。这不会自动完成，但它将起作用。 - David Andres
您的 OP 在项目表中具有类别 ID 和图像，因此这是我所依据的。 - David Andres
谢谢David。我不明白这行代码的意思：WHERE C.CategoryId = :category_id。:category_id是什么意思？我在示例中将其用作占位符来代表category_id，但我认为此查询应跨越所有类别，因此不需要WHERE语句。 - justinl
我也收到了错误 #1221 - UNION 和 ORDER BY 的使用不正确。 - justinl
显示剩余4条评论
0

下面的代码展示了一种使用循环完成它的方法。虽然它需要进行大量编辑，但我希望它能有所帮助。

        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
-1

不太好看，但是：

SELECT image 
FROM item 
WHERE date_listed IN (SELECT date_listed 
                      FROM item 
                      ORDER BY date_listed DESC LIMIT 4)
这需要为每个类别调用，对吗？有没有一种方法将所有内容分组到一个查询中？ - justinl
哎呀，我不知道在子查询中不能使用LIMIT。 - tster
1
这个问题的另一个难点是：多个图像可能具有相同的date_listed日期，你可能会得到不正确的数据。 - Steve McLeod
你可以在子查询中使用限制，只需要是1的限制。 - Jage
-2

经过谷歌搜索，快速答案是至少在mysql上不可能。

这个线程是 参考。

如果你担心使服务器崩溃并希望代码执行更好，也许你应该缓存该查询的结果。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接