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

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category.

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

For Example:

例如：

Pet Supplies

宠物用品

img1
img2
img3
img4

Pet Food

宠物食品

img1
img2
img3
img4

I know that I could easily solve this problem by querying the database for each category like so:

我知道我可以通过查询每个类别的数据库轻松解决这个问题，如下所示：

SELECT id FROM category

Then iterating over that data and querying the database for each category to grab the newest items:

然后迭代该数据并查询每个类别的数据库以获取最新项：

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

What I'm trying to figure out is if I can just use 1 query and grab all of that data. I have 33 categories so I thought perhaps it would help reduce the number of calls to the database.

我想弄清楚的是，如果我可以使用1个查询并获取所有数据。我有33个类别，所以我想也许这有助于减少对数据库的调用次数。

Anyone know if this is possible? Or if 33 calls isn't that big a deal and I should just do it the easy way.

有人知道这是否可行？或者如果33次通话并不是那么大，我应该这么简单。

7 个解决方案

#1

This is the greatest-n-per-group problem, and it's a very common SQL question.

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

Here's how I solve it with outer joins:

这是我用外连接解决它的方法：

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;

I'm assuming the primary key of the item table is item_id, and that it's a monotonically increasing pseudokey. That is, a greater value in item_id corresponds to a newer row in item.

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

Here's how it works: for each item, there are some number of other items that are newer. For example, there are three items newer than the fourth newest item. There are zero items newer than the very newest item. So we want to compare each item (i1) to the set of items (i2) that are newer and have the same category as i1. If the number of those newer items is less than four, i1 is one of those we include. Otherwise, don't include it.

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

The beauty of this solution is that it works no matter how many categories you have, and continues working if you change the categories. It also works even if the number of items in some categories is fewer than four.

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

Another solution that works but relies on the MySQL user-variables feature:

另一个有效的解决方案依赖于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 introduced support for SQL standard window functions. Now we can solve this sort of problem the way other RDBMS do:

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;

#2

This solution is an adaptation from another SO solution, thank you RageZ for locating this related/similar question.

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

NOTE

注意

This solution seems satisfactory for Justin's use case. Depending on your use case you may want to check Bill Karwin or David Andres' solutions in this posting. Bill's solution has my vote! See why, as I put both queries next to one another ;-)

对Justin的用例来说，这个解决方案似乎令人满意。根据您的使用案例，您可能需要在此帖子中查看Bill Karwin或David Andres的解决方案。比尔的解决方案有我的投票！看看为什么，因为我把两个查询放在一起;-)

The benefit of my solution is that it returns one record per category_id (the info from the item table is "rolled-up"). The main drawback of my solution is its lack of readability and its growing complexity as the number of desired rows grows (say to have 6 rows per category rather than 6). Also it may be slightly slower as the number of rows in the item table grows. (Regardless, all solutions will perform better with a smaller number of eligible rows in the item table, and it is therefore advisable to either periodically delete or move older items and/or to introduce a flag to help SQL filter out rows early)

我的解决方案的好处是它为每个category_id返回一条记录（项目表中的信息是“累计”）。我的解决方案的主要缺点是缺乏可读性，并且随着所需行数的增加而增加复杂性（比如说每个类别有6行而不是6行）。此外，随着项目表中行数的增加，它可能会略微变慢。（无论如何，所有解决方案都会在项目表中使用较少数量的符合条件的行时表现更好，因此建议您定期删除或移动较旧的项目和/或引入标记以帮助SQL尽早过滤掉行）

First try (didn't work!!!)...

第一次尝试（没有工作!!!）......

The problem with this approach was that the subquery would [rightfully but bad for us] produce very many rows, based on the cartesian products defined by the self joins...

这种方法的问题在于子查询[对我们来说是正确但不好]会产生很多行，基于自连接定义的笛卡尔积...

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;

Second try. (works ok!)

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

A WHERE clause in added for the subquery, forcing the date listed to be the latest, second latest, thrird lateest etc. for i1, i2, i3 etc. respectively (and also allowing for the null cases when there are fewer than 4 items for a given category id). Also added was unrelated filter clauses to prevent showing entries that are "sold" or entries that do not have an image (added requirements)

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

This logic makes the assumption that there are no duplicate date listed values (for a given category_id). Such cases would otherwise create duplicate rows. Effectively this use of the date listed is that of a monotonically incremented primary key as defined/required in Bill's solution.

该逻辑假设没有重复的日期列出值（对于给定的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;

Now... compare the following where I introduce an item_id key and use Bill's solution to provide the list of these to the "outside" query. You can see why Bill's approach is better...

现在......比较以下我介绍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

#3

In other databases you can do this using the ROW_NUMBER function.

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

Unfortunately MySQL does not support the ROW_NUMBER function, but you can emulate it using variables:

不幸的是，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

See it working online: sqlfiddle

看到它在线工作：sqlfiddle

It works as follows:

它的工作原理如下：

Intially @prev is set to NULL, and @rn is set to 0.
最初@prev设置为NULL，@ rn设置为0。
For each row we see, check if the category_id is the same as the previous row.
- If yes, increment the row number.
- 如果是，请递增行号。
- Otherwise start a new category and reset the row number back to 1.
- 否则，请启动新类别并将行号重置为1。
对于我们看到的每一行，检查category_id是否与上一行相同。如果是，请递增行号。否则，请启动新类别并将行号重置为1。
When the subquery completes, the final step is to filter so that only rows with row number less than or equal to 4 are kept.
子查询完成后，最后一步是过滤，以便只保留行号小于或等于4的行。

#4

not very pretty but:

不是很漂亮但是：

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

#5

Depending on how constant your categories are, the following is the simplest route

根据您的类别的不变程度，以下是最简单的路线

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

#6

the code below shows a way to do it in a loop it definetely needs a lot of editing, but i hope it helps.

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

        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

-1

ok after a googling the quick answer would it's not possible at least on mysql

谷歌搜索快速回答之后好吧至少在mysql上是不可能的

this this thread for reference

这个帖子供参考

maybe you should cache the result of that query if you are afraid to make fall down the server and you want the code to perform more well

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

#1