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

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

例如:

img1

img2

img3

img4

img1

img2

img3

img4

我知道我可以通过查询数据库来轻松解决此问题,如下所示:

选择类别的ID

然后遍历该数据并查询数据库中的每个类别以获取最新的项目:

从项目中选择图像,其中category_id =:category_id 

ORDER BY date_listed DESC LIMIT 4

我要弄清楚的是,我是否可以仅使用1个查询并获取所有这些数据。我有33个类别,因此我认为这可能有助于减少对数据库的调用次数。

有人知道这是否可能吗?或者,如果33个电话没什么大不了的,那么我应该以简单的方式进行。

回答:

这是每组最大的问题,也是一个非常常见的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对应于中的新行item

它是这样工作的:对于每个项目,还有一些其他较新的项目。例如,有三个项目比第四个最新项目新。有零个项目比最新的项目新。因此,我们想将每个项目(i1)与i2较新的项目集()进行比较,并具有与相同的类别i1。如果这些较新的项目的数量少于四个,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;

以上是 如何选择每个类别中最新的四个项目? 的全部内容, 来源链接: utcz.com/qa/413023.html

回到顶部