如何使用单个 SQL 查询检索每个类别的四个最新项目？

使用单个查询选择每个类别中的最新项目

在一个按名为category_id的字段分类的项目数据库中，任务是检索一个类别列表，每个类别都包含其四个最近列出的项目。与其为每个类别单独查询数据库，不如使用单个SQL查询来优化数据库调用。

使用外部联接的解决方案：

以下查询使用外部联接来识别和排除在同一类别中具有更新对应项目的项目：

<code class="language-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.category_id, i1.item_id
HAVING COUNT(*) <= 4;</code>

此查询使用LEFT OUTER JOIN将每个项目（i1）与其更新且具有相同类别的项目集（i2）连接起来。COUNT(*)用于计算每个类别中每个项目的匹配项数量。HAVING子句过滤掉具有超过四个匹配项的项目，从而确保仅选择每个类别中的四个最新项目。

使用MySQL用户变量的解决方案：

此解决方案利用MySQL的用户变量功能来跟踪组号和行号：

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, date_listed DESC
) AS t
WHERE t.rownum <= 4;</code>

在此查询中，用户定义的变量@g和@r用于跟踪当前类别和行号，确保仅选择每个类别中的前四个项目。

使用MySQL窗口函数的解决方案 (MySQL 8.0.3 )：

MySQL 8.0.3引入了对SQL标准窗口函数的支持，提供了一种更简洁高效的解决方案：

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY date_listed DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

此查询使用PARTITION BY category_id ORDER BY date_listed DESC子句按类别对结果集进行分区，并按每个分区中的date_listed列降序排列项目。然后，ROW_NUMBER()窗口函数为每个分区分配连续的行号，从而能够选择每个类别的前四个项目。

以上是如何使用单个 SQL 查询检索每个类别的四个最新项目？的详细内容。更多信息请关注PHP中文网其他相关文章！