如何使用單一 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中文網其他相關文章！