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

Question

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

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

Answer 1

这是每个组中最大的n个问题，这是一个非常常见的SQL问题。下面是我如何用外部连接来解决这个问题：SELECT&nbsp;i1.*FROM&nbsp;item&nbsp;i1LEFT&nbsp;OUTER&nbsp;JOIN&nbsp;item&nbsp;i2&nbsp;&nbsp;ON&nbsp;(i1.category_id&nbsp;=&nbsp;i2.category_id&nbsp;AND&nbsp;i1.item_id&nbsp;<&nbsp;i2.item_id)GROUP&nbsp;BY&nbsp;i1. item_idHAVING&nbsp;COUNT(*)&nbsp;<&nbsp;4ORDER&nbsp;BY&nbsp;category_id,&nbsp;date_listed;我假设item表是item_id，这是一个单调递增的伪键。也就是说，在item_id中的较新行对应。item.下面是它的工作原理：对于每一项，都有一些更新的其他项目。例如，有三个项目比第四个最新项目更新。有零个项目比最新的项目更新。所以我们想比较每个项目(i1)到一组项目(i2)是更新的，并且具有与i1..如果这些新产品的数量少于四个，i1就是其中之一。否则，不要包括它。这个解决方案的美妙之处在于，不管您有多少类别，它都能工作，如果您更改了类别，它将继续工作。即使某些类别中的项目数少于4个，它也能工作。另一个可行但依赖MySQL用户变量特性的解决方案：SELECT&nbsp;*FROM&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp;i.*,&nbsp;@r&nbsp;:=&nbsp;IF(@g&nbsp;=&nbsp;category_id,&nbsp;@r+1,&nbsp;1)&nbsp;AS&nbsp;rownum,&nbsp;@g&nbsp;:=&nbsp;category_id&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;(@g:=null,&nbsp;@r:=0)&nbsp;AS&nbsp;_init&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;CROSS&nbsp;JOIN&nbsp;item&nbsp;i&nbsp;&nbsp;&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;i.category_id,&nbsp;i.date_listed)&nbsp;AS&nbsp;tWHERE&nbsp;t.rownum&nbsp;<=&nbsp;3;MySQL 8.0.3引入了对SQL标准窗口函数的支持。现在，我们可以像其他RDBMS那样解决这类问题：WITH&nbsp;numbered_item&nbsp;AS&nbsp;( &nbsp;&nbsp;SELECT&nbsp;*,&nbsp;ROW_NUMBER()&nbsp;OVER&nbsp;(PARTITION&nbsp;BY&nbsp;category_id&nbsp;ORDER&nbsp;BY&nbsp;item_id)&nbsp;AS&nbsp;rownum&nbsp;&nbsp;FROM&nbsp;item)SELECT&nbsp;*&nbsp;FROM&nbsp;numbered_item&nbsp;WHERE&nbsp;rownum &nbsp;&nbsp;&nbsp;<=&nbsp;4;

Answer 2

在其他数据库中，可以使用ROW_NUMBER功能。SELECT &nbsp;&nbsp;&nbsp;&nbsp;category_id,&nbsp;image,&nbsp;date_listedFROM( &nbsp;&nbsp;&nbsp;&nbsp;SELECT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;category_id,&nbsp;image,&nbsp;date_listed, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROW_NUMBER()&nbsp;OVER&nbsp;(PARTITION&nbsp;BY&nbsp;category_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;date_listed&nbsp;DESC)&nbsp;AS&nbsp;rn&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;item)&nbsp;AS&nbsp;T1WHERE&nbsp;rn&nbsp;<=&nbsp;4不幸的是，MySQL不支持ROW_NUMBER函数，但是您可以使用变量来模拟它：SELECT &nbsp;&nbsp;&nbsp;&nbsp;category_id,&nbsp;image,&nbsp;date_listedFROM( &nbsp;&nbsp;&nbsp;&nbsp;SELECT &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;category_id,&nbsp;image,&nbsp;date_listed, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@rn&nbsp;:=&nbsp;IF(@prev&nbsp;=&nbsp;category_id,&nbsp;@rn&nbsp;+&nbsp;1,&nbsp;1)&nbsp;AS&nbsp;rn, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@prev&nbsp;:=&nbsp;category_id&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;item&nbsp;&nbsp;&nbsp;&nbsp;JOIN&nbsp;(SELECT&nbsp;@prev&nbsp;:=&nbsp;NULL,&nbsp;@rn&nbsp;=&nbsp;0)&nbsp;AS&nbsp;vars&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER&nbsp;BY&nbsp;category_id,&nbsp;date_listed&nbsp;DESC)&nbsp;AS&nbsp;T1WHERE&nbsp;rn&nbsp;<=&nbsp;4看到它在网上工作：木琴它的工作如下：intially@prev设置为null，@rn设置为0。对于我们看到的每一行，检查类别_id是否与前一行相同。如果是，增加行号。否则，启动一个新类别并将行号重置为1。子查询完成后，最后一步是过滤，以便只保留行号小于或等于4的行。