您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL 去重保留最大值：NOT EXISTS 相關子查詢 vs 內連接派生表兩種標準寫法

編輯：關於SqlServer

需求

表 Sheet1 (ID, PID, ...)，同 PID 保留 ID 最大的那一行，刪除其餘。

寫法一：NOT EXISTS 相關子查詢 (直觀、易讀)

SELECT * FROM Sheet1 a
WHERE NOT EXISTS (
    SELECT 1 FROM Sheet1 b 
    WHERE b.PID = a.PID AND b.ID > a.ID
);
-- 邏輯：不存在「同 PID 且 ID 更大」的行 → 即為該 PID 組的最大 ID

寫法二：內連接派生表 (效能通常更優)

SELECT a.* 
FROM Sheet1 a
INNER JOIN (
    SELECT PID, MAX(ID) AS MaxID 
    FROM Sheet1 
    GROUP BY PID
) b ON a.PID = b.PID AND a.ID = b.MaxID;

效能對比與選型

維度	NOT EXISTS	內連接派生表
可讀性	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
索引利用	需 (PID, ID) 複合索引	同樣需求，但 GROUP BY 可用流聚合
大資料量	相關子查詢逐行求值	派生表先聚合再連接，通常更快
並行計畫	難並行	易並行 (Hash Match / Merge Join)
推薦場景	小表、臨時查詢、邏輯複雜	大表、生產環境、固定報表

刪除重複資料 (保留最大 ID)

-- 方法 1：NOT EXISTS
DELETE FROM Sheet1 
WHERE NOT EXISTS (
    SELECT 1 FROM Sheet1 b 
    WHERE b.PID = Sheet1.PID AND b.ID > Sheet1.ID
);

-- 方法 2：CTE + ROW_NUMBER (SQL 2005+，最通用)
WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PID ORDER BY ID DESC) AS rn
    FROM Sheet1
)
DELETE FROM Ranked WHERE rn > 1;

-- 方法 3：內連接 (需支援 FROM 刪除)
DELETE a FROM Sheet1 a
LEFT JOIN (
    SELECT PID, MAX(ID) AS MaxID FROM Sheet1 GROUP BY PID
) b ON a.PID = b.PID AND a.ID = b.MaxID
WHERE b.MaxID IS NULL;

延伸：多欄位分組去重

-- 同 (PID, Category) 保留最大 ID
SELECT a.* FROM Sheet1 a
JOIN (
    SELECT PID, Category, MAX(ID) AS MaxID 
    FROM Sheet1 GROUP BY PID, Category
) b ON a.PID = b.PID AND a.Category = b.Category AND a.ID = b.MaxID;