萬盛學電腦網

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

SQL 过滤重复数据实战:去重查询、保留最大ID、多字段联合主键技巧

SQL 过滤重复数据实战:去重查询、保留最大ID、多字段联合主键技巧

業務查詢常需「按某欄位去重但保留完整行」或「按聯合鍵去重並保留最大 ID」。本文總結五種實用模式,涵蓋臨時表法、聚合函數法、子查詢連接法、字符串拼接法、窗口函數法,附帶實際業務場景對比。

場景:書籍表去重

id  bookname          cbs               zz
1   ASP教程大全       清華大學出版社      田正平
2   數據庫系統教程    高等教育出版社      周奧英
3   ASP大全           清華大學出版社      田正平

需求:按 bookname 去重,顯示 bookname, cbs, zz,保留任意一行(或 ID 最大行)。

方法一:臨時表 + 身份列法(SQL Server / MySQL 8.0-)

SELECT IDENTITY(INT,1,1) AS id, * INTO #temp FROM tabel;
SELECT * FROM #temp 
WHERE id IN (
    SELECT MAX(id) FROM #temp 
    GROUP BY col1, col2, col3 HAVING COUNT(*) > 1
);

原理:人工加行號 → 分組取最大行號 → 反查明細。

方法二:聚合函數 + GROUP BY(需顯示所有列)

SELECT MAX(id) AS id, MAX(bookname) AS bookname, 
       MAX(cbs) AS cbs, MAX(zz) AS zz, new_id
FROM table 
GROUP BY new_id;

⚠️ 前提:new_id 為去重依據,其他列對同組取 MAX() 無語義衝突。若需保留「同組內某行的完整原貌」,此法不適用。

方法三:字符串拼接模擬聯合主鍵(舊版本通用)

SELECT * FROM table 
WHERE col1+','+col2+','+col3+','+col4+','+col5 IN (
    SELECT MAX(col1+','+col2+','+col3+','+col4+','+col5)
    FROM table 
    GROUP BY col1,col2,col3,col4,col5
    HAVING COUNT(*) > 1
);

將多列拼接成單字符串比較,兼容不支援 ROW_NUMBER() 的老版本。

方法四:子查詢連接法(標準 SQL,保留最小/最大 ID 行)

SELECT t1.bookname, t1.zz, t1.cbs
FROM table t1
JOIN (
    SELECT MIN(id) AS id, bookname  -- 或 MAX(id) 保留最新
    FROM table 
    GROUP BY bookname
) t2 ON t1.bookname = t2.bookname AND t1.id = t2.id;

✅ 優點:標準 SQL,可精確控制保留哪一行(最早/最晚),無臨時表。
✅ 擴展:GROUP BY bookname, cbs 即為聯合鍵去重。

方法五:窗口函數 ROW_NUMBER()(MySQL 8.0+、PostgreSQL、Oracle、SQL Server 通用)

WITH ranked AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY bookname ORDER BY id DESC) AS rn
    FROM book_table
)
SELECT bookname, cbs, zz
FROM ranked 
WHERE rn = 1;
  • PARTITION BY 定義去重鍵(單列或多列)。
  • ORDER BY 決定保留策略:id DESC 保留最大 ID,create_time ASC 保留最早創建。
  • 單掃描、無臨時表、可直接用於 DELETE 刪除重複行。

六、效能對比與選型建議

方法適用資料量可保留指定行跨庫通用推薦指數
臨時表 + IDENTITY中小表✅❌ (SQL Server)⭐⭐
聚合函數 GROUP BY小表❌ (只能取聚合值)✅⭐
字符串拼接小表✅✅⭐⭐
子查詢連接中表✅✅⭐⭐⭐⭐
窗口函數 ROW_NUMBER大表/生產✅✅ (8.0+)⭐⭐⭐⭐⭐

七、生產環境刪除重複行模板(MySQL 8.0+)

-- 分批刪除,避免長事務鎖表
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY biz_key ORDER BY create_time DESC) rn
    FROM big_table
)
DELETE FROM big_table 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1)
LIMIT 10000;  -- MySQL 8.0.19+ 支援 LIMIT
copyright © 萬盛學電腦網 all rights reserved