MySQL 刪除重複記錄：保留每用戶最近兩條

問題描述

表結構：userid, score, year。需對每個用戶，按 year 降序保留最新兩條記錄，刪除其餘。要求單條 SQL 完成，且保留條數可配置（不固定為 2）。

解決思路

使用窗口函數 ROW_NUMBER()（MySQL 8.0+）或變量模擬（舊版本）為每組內記錄編號，再刪除編號大於 N 的行。

MySQL 8.0+ 寫法

DELETE t
FROM `scores` t
JOIN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY userid ORDER BY year DESC) AS rn
        FROM `scores`
    ) tmp
    WHERE rn > 2  -- 此處改為所需保留條數
) d ON t.id = d.id;

MySQL 5.7 兼容寫法

DELETE t
FROM `scores` t
JOIN (
    SELECT s1.id
    FROM `scores` s1
    JOIN `scores` s2
      ON s1.userid = s2.userid AND s1.year <= s2.year
    GROUP BY s1.id
    HAVING COUNT(*) > 2  -- 保留條數
) d ON t.id = d.id;

注意事項

• 確保表有主鍵 id，否則需用 userid, year, score 聯合鍵關聯。
• 大量數據時建議分批刪除，避免長事務鎖表。
• 執行前先用 SELECT 驗證將刪除的記錄。