SQL Server 分組取最大值：歷史記錄去重查詢語句

需求場景

表含混合主鍵 (iworkitemid, historyworkitemid)，需按 iworkitemid 分組，保留 historyworkitemid 最大的記錄，其餘捨棄。

標準解法

SELECT MAX(historyworkitemid) AS max_history_id, iworkitemid
FROM your_table
GROUP BY iworkitemid;

若需保留完整行（其他列）

-- 方法 1：ROW_NUMBER() 窗口函數 (SQL Server 2005+)
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY iworkitemid ORDER BY historyworkitemid DESC) AS rn
    FROM your_table
)
SELECT * FROM ranked WHERE rn = 1;

-- 方法 2：相關子查詢
SELECT t1.*
FROM your_table t1
WHERE historyworkitemid = (
    SELECT MAX(historyworkitemid)
    FROM your_table t2
    WHERE t2.iworkitemid = t1.iworkitemid
);

-- 方法 3：JOIN 子查詢
SELECT t1.*
FROM your_table t1
JOIN (
    SELECT iworkitemid, MAX(historyworkitemid) AS max_id
    FROM your_table
    GROUP BY iworkitemid
) t2 ON t1.iworkitemid = t2.iworkitemid AND t1.historyworkitemid = t2.max_id;

性能建議

• 在 (iworkitemid, historyworkitemid) 上建立索引，窗口函數與 JOIN 均可高效利用。
• 數據量極大時，考慮分批處理或建立物化視圖。