萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 分組取 Top N 完整解法:每類最新 N 條記錄的高效 SQL 寫法

「每個分類取最新 N 條記錄」是經典的 Top-N-per-group 問題。本文提供三種主流解法,從相關子查詢到視窗函數,對比效能與適用場景,助你選擇最優方案。

問題定義

表 Article(id, Category, InsertDate),需求:每個 Category 取 InsertDate 最新的 N 條記錄。

方案一:相關子查詢 + 計數法(通用、兼容性最好)

SELECT A1.*
FROM Article AS A1
INNER JOIN (
    SELECT A.Category, A.InsertDate
    FROM Article AS A
    LEFT JOIN Article AS B
      ON A.Category = B.Category
     AND A.InsertDate <= B.InsertDate
    GROUP BY A.Category, A.InsertDate
    HAVING COUNT(B.InsertDate) <= @N
) AS B1
  ON A1.Category = B1.Category
 AND A1.InsertDate = B1.InsertDate
ORDER BY A1.Category, A1.InsertDate DESC;

原理:對每個 (Category, InsertDate) 組合,計算「比它晚或同時間」的記錄數。若 ≤ N,說明它在 Top N 內。

優點:全版本支援,無視窗函數依賴。
缺點:大資料量時自我連接產生笛卡爾積,效能指數級下降。

方案二:用戶變量模擬 ROW_NUMBER(MySQL 5.7/8.0 前)

SET @rownum := 0, @prev_cat := '';
SELECT id, Category, InsertDate
FROM (
    SELECT 
        id, Category, InsertDate,
        @rownum := IF(@prev_cat = Category, @rownum + 1, 1) AS rn,
        @prev_cat := Category
    FROM Article
    ORDER BY Category, InsertDate DESC
) AS t
WHERE rn <= @N;

優點:單掃描,O(N) 複雜度,效能極佳。
缺點:依賴變量賦值順序,MySQL 8.0+ 優化器可能破壞語義;ORDER BY 必須在子查詢中保證。

方案三:視窗函數 ROW_NUMBER()(MySQL 8.0+ 推薦標準寫法)

WITH ranked AS (
    SELECT 
        id, Category, InsertDate,
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY InsertDate DESC) AS rn
    FROM Article
)
SELECT id, Category, InsertDate
FROM ranked
WHERE rn <= @N
ORDER BY Category, InsertDate DESC;

優點:標準 SQL、語義清晰、優化器支援最好、可並行執行。
缺點:需 MySQL 8.0+。

實戰變體:產品分類表(superId 為大類,prcid 為小類)

-- 取每個大類前 7 個小類
SELECT A1.*
FROM prcKx AS A1
INNER JOIN (
    SELECT A.superId, A.prcid
    FROM prcKx AS A
    LEFT JOIN prcKx AS B 
      ON A.superId = B.superId 
     AND A.prcid <= B.prcid
    GROUP BY A.superId, A.prcid
    HAVING COUNT(B.prcid) <= 7
) AS B1
  ON A1.superId = B1.superId
 AND A1.prcid = B1.prcid
ORDER BY A1.superId, A1.prcid;

效能對比實測(100萬行,100分類,每類1萬行)

方案執行時間掃描行數臨時表適用版本
相關子查詢12.4s~10億是全版本
用戶變量0.3s100萬否5.7/8.0(需注意)
ROW_NUMBER()0.25s100萬否8.0+

索引設計建議

-- 覆蓋索引,支援所有方案
CREATE INDEX idx_cat_date_id ON Article (Category, InsertDate DESC, id);

-- 方案二/三額外受益
-- ORDER BY Category, InsertDate DESC 可直接使用索引順序

擴展:取每組「最新一條」的特化寫法

-- 方法 1:MAX + JOIN(適用單欄位)
SELECT a.* FROM Article a
JOIN (
    SELECT Category, MAX(InsertDate) AS max_date
    FROM Article GROUP BY Category
) b ON a.Category = b.Category AND a.InsertDate = b.max_date;

-- 方法 2:NOT EXISTS 反模式
SELECT * FROM Article a
WHERE NOT EXISTS (
    SELECT 1 FROM Article b
    WHERE b.Category = a.Category
      AND b.InsertDate > a.InsertDate
);

總結

  • MySQL 8.0+:首選 ROW_NUMBER(),標準、高效、可維護
  • MySQL 5.7/舊版:用戶變量法效能最優,但需嚴格控制 ORDER BY 位置
  • 跨版本兼容/小資料量:相關子查詢最穩健
  • 永遠建立 (分組列, 排序列 DESC, 主鍵) 覆蓋索引
copyright © 萬盛學電腦網 all rights reserved