SQL如何删除重复的记录，只保留一条？

admin 2023-10-15 581 阅读 0评论

一、关于mysql表中数据重复

SQL如何删除重复的记录，只保留一条？这是面试题中容易出现的一个问题，也是我们清理数据库脏数据会遇到的问题。

关于删除mysql表中重复数据问题，本文中给到两种办法：聚合函数、窗口函数row_number()的方法。
(注意：MySQL从8.0开始支持窗口函数)

测试数据准备：首先创建一个测试表test，插入一些测试数据，模拟一些重复数据（最终目标：删除重复数据，但不处理null行）

先查询下重复数据，确认待处理数据的数量，然后开始处理：

SELECT
        seq_id,
        out_user_code,
        COUNT( out_user_code ) count
FROM
        test
WHERE
        is_deleted = 0
        AND out_user_code IS NOT NULL
GROUP BY
        out_user_code
HAVING
        count( out_user_code )> 1

二、聚合函数min(id)+not in

思路：首先通过子查询取出 id 最小的不重复行,然后通过 not in 删除重复数据

1、首先查询一下 id 最小的不重复行（我们留下最早插入的数据，后面的重复数据都删除）:

SELECT
    min(seq_id) seq_id,
	out_user_code,
COUNT( out_user_code ) count 
FROM
	test 
GROUP BY
	out_user_code

2、通过查询结果可知，重复的数据行seq_id为2、7的数据过滤掉了，接下来NOT IN 操作应该删除2、7重复数据行。那按照假设想法执行NOT IN：

DELETE from test where r.seq_id not in (
SELECT
min(t.seq_id) seq_id
FROM
		test  t
GROUP BY
		t.out_user_code
)  r

会发现报错：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r' at line 8, Time: 0.007000s

原因：不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

解决方案：可将SELECT出的结果再通过中间表SELECT一遍。

3、最终处理sql：

DELETE from test where seq_id not in (
SELECT r.seq_id from (
SELECT
		   min(t.seq_id) seq_id
FROM
			test  t
GROUP BY
			t.out_user_code
)  r 
) and out_user_code is not null

换种写法（保证相关字段有索引）：

DELETE from test 
where
out_user_code in (select * from (select out_user_code from test del group by out_user_code HAVING count(out_user_code) >1)a)
and seq_id not in(select * from (select min(seq_id) id from test del group by out_user_code  HAVING count(out_user_code) >1)b
)f

提醒：能逻辑删除尽量不要物理删除。

三、窗口函数row_number()

思路：通过 PARTITION BY 对列进行分区排序并生成序号列,然后将序号大于 1 的行删除,row_number() over partition by。

1、分区查询：

SELECT
	ROW_NUMBER() OVER ( PARTITION BY out_user_code ORDER BY seq_id ) num,
	out_user_code 
FROM
	test 
WHERE
	out_user_code IS NOT NULL

知识补充：
1、ROW_NUMBER:对结果集的输出进行编号，是运行查询时计算出的临时值。具体来说，返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
2、ROW_NUMBER() 具有不确定性。除非以下条件成立，否则不保证在每次执行时，使用 ROW_NUMBER() 的查询所返回行的顺序都完全相同。
1）分区列的值是唯一的。
2）ORDER BY 列的值是唯一的。
3）分区列和 ORDER BY 列的值的组合是唯一的。

2、直接尝试删除num>1的数据：

DELETE a 
FROM (
SELECT
	 ROW_NUMBER() OVER (PARTITION BY out_user_code ORDER BY seq_id) num 
FROM test 
where out_user_code IS NOT NULL
) a 
WHERE num>1

会发现报错：
1288 - The target table a of the DELETE is not updatable, Time: 0.007000s

原因同上，同样的我们换个方式处理一下。给窗口指定别名：WINDOW w AS (PARTITION BY 字段1 ORDER BY 字段2)

3、最终处理sql:

DELETE
FROM test
WHERE seq_id in (
SELECT seq_id
FROM(
SELECT *
FROM (
SELECT ROW_NUMBER() OVER w AS row_num,seq_id
FROM test  where out_user_code is not null
			WINDOW w AS (PARTITION BY out_user_code ORDER BY seq_id)
)t
	    WHERE row_num >1
)e
)