MySQL實現(xiàn)去重的幾種方法小結(jié)

更新時間：2024年07月30日 09:45:27 作者：碼農(nóng)研究僧

在MySQL中,SELECT DISTINCT 和 GROUP BY 可以用來去除重復(fù)記錄,二者有相似的功能,但在某些情況下有所不同,本文將通過代碼示例給大家詳細(xì)介紹這幾種方法,感興趣的小伙伴跟著小編一起來看看吧

前言

在MySQL中，SELECT DISTINCT 和 GROUP BY 可以用來去除重復(fù)記錄，二者有相似的功能，但在某些情況下有所不同

1. DISTINCT

SELECT DISTINCT 用于從表中選擇唯一的記錄，去除所有重復(fù)的數(shù)據(jù)行

直接作用于結(jié)果集，并去除所有指定列上的重復(fù)值

SELECT DISTINCT equipment_no
FROM equipment_check_order;

在單列去重的場景下，效率較高。但是在處理大數(shù)據(jù)集時，可能會影響性能，因為需要全表掃描和去重

2. GROUP BY

GROUP BY 用于將具有相同值的記錄分組，并可以進(jìn)行聚合操作

即使不使用聚合函數(shù)，僅使用 GROUP BY 也可以達(dá)到去重的效果

SELECT equipment_no
FROM equipment_check_order
GROUP BY equipment_no;

3. Mysql版本

3.1 低版本

在不支持窗口函數(shù)的 MySQL 版本中，可以使用子查詢來進(jìn)行去重

假設(shè)希望去除 equipment_check_order 表中的重復(fù) equipment_no，并保留每個 equipment_no 的最新記錄，可以使用子查詢的方式來實現(xiàn)

-- 創(chuàng)建目標(biāo)表
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 將去重后的數(shù)據(jù)插入到目標(biāo)表
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT equipment_no, check_date
FROM equipment_check_order AS e
WHERE check_date = (
    SELECT MAX(check_date)
    FROM equipment_check_order
    WHERE equipment_no = e.equipment_no
);

或者使用自聯(lián)拼接的方式:

-- 創(chuàng)建目標(biāo)表
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 使用自聯(lián)接將去重后的數(shù)據(jù)插入到目標(biāo)表
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT e1.equipment_no, e1.check_date
FROM equipment_check_order e1
LEFT JOIN equipment_check_order e2
ON e1.equipment_no = e2.equipment_no
AND e1.check_date < e2.check_date
WHERE e2.check_date IS NULL;

3.2 高版本

使用 ROW_NUMBER() 和 WITH 子句進(jìn)行去重

基本步驟：

定義公共表表達(dá)式（CTE）：使用 WITH 子句創(chuàng)建一個臨時結(jié)果集
應(yīng)用 ROW_NUMBER() 函數(shù)：在 CTE 內(nèi)部為每一行分配唯一的行號
選擇需要的記錄：在外層查詢中，根據(jù)行號篩選出需要的記錄

示例：保留每個 equipment_no 的最新記錄
假設(shè)有一個表 equipment_check_order，包含 equipment_no 和 check_date 列，希望去除重復(fù)的 equipment_no，并保留每個 equipment_no 的最新記錄（即 check_date 最大的記錄）

-- 創(chuàng)建目標(biāo)表（如果不存在）
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 使用 CTE 和 ROW_NUMBER() 進(jìn)行去重
WITH ranked AS (
  SELECT equipment_no,
         check_date,
         ROW_NUMBER() OVER (PARTITION BY equipment_no ORDER BY check_date DESC) AS rn
  FROM equipment_check_order
)
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT equipment_no, check_date
FROM ranked
WHERE rn = 1;

PARTITION BY equipment_no：根據(jù) equipment_no 列進(jìn)行分組
SELECT equipment_no, check_date FROM ranked WHERE rn = 1：從 CTE ranked 中選擇行號為 1 的記錄，即每個分組中最新的記錄，如果為小于5，代表去重并保留多條記錄

4. 總結(jié)

方法	用法示例	優(yōu)點	缺點	適用場景
SELECT DISTINCT	SELECT DISTINCT equipment_no FROM equipment_check_order;	簡單易用，直觀	對大數(shù)據(jù)集性能影響可能較大	單列去重，簡單查詢
GROUP BY	SELECT equipment_no FROM equipment_check_order GROUP BY equipment_no;	靈活，與聚合函數(shù)結(jié)合使用方便	語法復(fù)雜，對性能影響與數(shù)據(jù)量有關(guān)	復(fù)雜查詢或需要與聚合函數(shù)結(jié)合使用的場景
子查詢 + ROW_NUMBER()		高靈活性，可選擇特定記錄	需使用窗口函數(shù)，語法復(fù)雜	高級去重，保留特定記錄
臨時表		適合復(fù)雜數(shù)據(jù)處理，多步驟數(shù)據(jù)操作	操作步驟多，占用額外存儲空間	數(shù)據(jù)清理、遷移，復(fù)雜操作