更新时间:2024-06-11 GMT+08:00

如何删除重复的表数据?

清理数据库脏数据时,可能会有多条重复数据只保留一条的场景,此场景可以使用聚合函数或窗口函数来实现。

构建表数据

  1. 创建表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');
    

  2. 查询表t_customer:

    1
    SELECT * FROM t_customer ORDER BY id;
    

当客户的名字、性别、邮件都相同时,则判定它们为重复的记录。对于表t_customer,id等于1、3、6的为重复数据,id为2、5的也是重复数据,删除多余的数据的同时需要保留其中的一条。

方法一:使用聚合函数min(expr)

使用聚合函数通过子查询取出id最小的不重复行,然后通过NOT IN删除重复数据。

  1. 查询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的数据被过滤掉了。

  2. 使用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
    );
    

  3. 查询删除重复数据后的表t_customer:

    1
    SELECT * FROM t_customer ORDER BY id;
    

    由返回结果可知,重复数据已被删除。

方法二:使用窗口函数row_number()

通过PARTITION BY对列进行分区排序并生成序号列,然后将序号大于1的行删除。

  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的数据即为重复数据。

  2. 删除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 )
    );
    

  3. 查询删除重复数据后的表t_customer:

    1
    SELECT * FROM t_customer ORDER BY id;