SQL分組去重實戰：ROW_NUMBER() OVER (PARTITION BY) 保留每組最新一條

經典需求：按userid, classid分組，每組保留addtime最新的一條（去重）。標準SQL用窗口函數ROW_NUMBER()優雅解決，適用於PostgreSQL、SQL Server、Oracle、MySQL 8.0+。

核心SQL模板

SELECT * FROM (
    SELECT 
        userid,
        classid,
        remark,
        ROW_NUMBER() OVER (
            PARTITION BY userid, classid
            ORDER BY addtime DESC
        ) AS row_bank
    FROM Eng24VIPCourseIntention
) AS ta
WHERE row_bank = 1;

關鍵子句解析

MySQL 5.7/舊版本替代方案

方案一：關聯子查詢（效能一般）

SELECT t1.* FROM Eng24VIPCourseIntention t1
JOIN (
    SELECT userid, classid, MAX(addtime) AS max_time
    FROM Eng24VIPCourseIntention
    GROUP BY userid, classid
) t2 ON t1.userid = t2.userid AND t1.classid = t2.classid AND t1.addtime = t2.max_time;

方案二：用戶變量模擬行號（技巧流，慎用）

SELECT userid, classid, remark FROM (
    SELECT 
        userid, classid, remark,
        @rn := IF(@prev = CONCAT(userid, classid), @rn + 1, 1) AS rn,
        @prev := CONCAT(userid, classid)
    FROM Eng24VIPCourseIntention
    CROSS JOIN (SELECT @rn := 0, @prev := '') vars
    ORDER BY userid, classid, addtime DESC
) t WHERE rn = 1;

進階變體

• 保留每組最舊：ORDER BY addtime ASC
• 保留每組最大/最小值行：ORDER BY score DESC
• 多欄位分組：PARTITION BY a, b, c
• 並行處理：大表可先CREATE TEMPORARY TABLE分區處理

效能提示

• 確保(userid, classid, addtime)有聯合索引，支援PARTITION BY與ORDER BY下推
• 大表先過濾再分組：WHERE addtime > '2023-01-01'減少參與行數
• 結果集較小時，窗口函數單次掃描極快

窗口函數讓「分組Top-N」從「多層嵌套子查詢」進化為「單層線性邏輯」，是現代SQL必備技能。