業務查詢常需「按某欄位去重但保留完整行」或「按聯合鍵去重並保留最大 ID」。本文總結五種實用模式,涵蓋臨時表法、聚合函數法、子查詢連接法、字符串拼接法、窗口函數法,附帶實際業務場景對比。
id bookname cbs zz
1 ASP教程大全 清華大學出版社 田正平
2 數據庫系統教程 高等教育出版社 周奧英
3 ASP大全 清華大學出版社 田正平
需求:按 bookname 去重,顯示 bookname, cbs, zz,保留任意一行(或 ID 最大行)。
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
);
原理:人工加行號 → 分組取最大行號 → 反查明細。
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() 的老版本。
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 即為聯合鍵去重。
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+) | ⭐⭐⭐⭐⭐ |
-- 分批刪除,避免長事務鎖表
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