SQL 去重完全指南：DISTINCT vs ROW_NUMBER() 實戰對比

場景準備：UserInfo 示例表

方案一：DISTINCT 全行去重

SELECT DISTINCT * FROM UserInfo;

結果：第 1 行與第 5 行完全重複，僅保留一條。第 3 行（同名不同年齡/城市）保留。

適用場景：完全重複行去重。局限：無法指定「按某幾列去重」。

方案二：ROW_NUMBER() 靈活去重

核心語法

SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS RowId,
        *
    FROM UserInfo
) x
WHERE x.RowId = 1;

關鍵字解析

• PARTITION BY Name：按 Name 分組，組內編號從 1 開始
• ORDER BY ID：組內按 ID 排序，決定保留哪一行
• RowId = 1：每組保留第一行（最小 ID）

執行結果

「張三」三條記錄僅保留 ID=1 的那行。

ROW_NUMBER() 進階用法：生成行號

SELECT 
    ROW_NUMBER() OVER (ORDER BY ID) AS RowNum,
    *
FROM UserInfo;

結果包含連續行號 1~5，可用於：

• 分頁查詢：WHERE RowNum BETWEEN 1 AND 15
• 抽樣統計：WHERE RowNum % 10 = 1 取 10% 樣本
• 排名展示

對比總結

MySQL 8.0 以前版本替代方案

-- 利用變量模擬 ROW_NUMBER()
SELECT * FROM (
    SELECT 
        @row_num := IF(@prev_name = Name, @row_num + 1, 1) AS RowId,
        @prev_name := Name AS Name,
        ID, Age, City
    FROM UserInfo, (SELECT @row_num := 0, @prev_name := '') vars
    ORDER BY Name, ID
) t WHERE RowId = 1;

最佳實踐建議

1. 全行去重優先用 DISTINCT，語義清晰且高效
2. 業務鍵去重（如按用戶 ID、訂單號）必須用 ROW_NUMBER()
3. 分頁查詢 MySQL 8.0+ 推薦 ROW_NUMBER()，舊版本用 LIMIT offset, size
4. 大數據量去重考慮 GROUP BY + 聚合函數或臨時表分批處理