清理数据库脏数据时，可能会有多条重复数据只保留一条的场景，此场景可以使用聚合函数或窗口函数来实现。
构建表数据
创建表t_customer，向表中插入包含重复记录的数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE t_customer (
id int NOT NULL,
cust_name varchar(32) NOT NULL COMMENT '名字',
gender varchar(10) NOT NULL COMMENT '性别',
email varchar(32) NOT NULL COMMENT 'email',
PRIMARY KEY (id)
) ;
INSERT INTO t_customer VALUES ('1', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO t_customer VALUES ('2', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('3', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO t_customer VALUES ('4', 'John', 'Male', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('5', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO t_customer VALUES ('6', 'Tom', 'Male', 'high_salary@sample.com');
查询表t_customer：
1
SELECT * FROM t_customer ORDER BY id;
当客户的名字、性别、邮件都相同时，则判定它们为重复的记录。对于表t_customer，id等于1、3、6的为重复数据，id为2、5的也是重复数据，删除多余的数据的同时需要保留其中的一条。
方法一：使用聚合函数min(expr)
使用聚合函数通过子查询取出id最小的不重复行，然后通过NOT IN删除重复数据。
查询id最小的不重复行：
1
2
3
4
5
6
7
8
SELECT
min(id) id,
cust_name,
gender,
COUNT( cust_name ) count
FROM t_customer
GROUP BY cust_name,gender
ORDER BY id;
通过查询结果可知，重复的数据行id为3、5、6的数据被过滤掉了。
使用NOT IN过滤重复数据行并删除：
1
2
3
4
5
6
DELETE from t_customer where id not in (
SELECT
min(id) id
FROM t_customer
GROUP BY cust_name,gender
);
查询删除重复数据后的表t_customer：
1
SELECT * FROM t_customer ORDER BY id;
由返回结果可知，重复数据已被删除。
方法二：使用窗口函数row_number()
通过PARTITION BY对列进行分区排序并生成序号列，然后将序号大于1的行删除。
分区查询，对列进行分区排序并生成序号列：
1
2
3
4
5
6
SELECT
id,
cust_name,
gender,
ROW_NUMBER() OVER (PARTITION BY cust_name,gender ORDER BY id) num
FROM t_customer;
由返回结果可知，num>1的数据即为重复数据。
删除num>1的数据：
1
2
3
4
5
6
7
8
DELETE FROM t_customer WHERE id in (
SELECT id FROM(
SELECT * FROM (
SELECT ROW_NUMBER() OVER w AS row_num,id
FROM t_customer
WINDOW w AS (PARTITION BY cust_name,gender ORDER BY id) )
WHERE row_num >1 )
);
查询删除重复数据后的表t_customer：
1
SELECT * FROM t_customer ORDER BY id;