创建测试数据

  • 测试数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    CREATE TABLE Person (
    	ID INT PRIMARY KEY,
    	NAME VARCHAR ( 20 ) NULL,
    	Age INT NULL,
    	Address VARCHAR ( 20 ) NULL,
    	Sex CHAR ( 2 ) NULL
    );

    INSERT INTO Person (ID,Name,Age,Address,Sex)
    VALUES
    ( 1, '张三', 18, '北京路18号', '男' ),
    ( 2, '李四', 19, '北京路29号', '男' ),
    ( 3, '王五', 19, '南京路11号', '女' ),
    ( 4, '张三', 18, '北京路18号', '男' ),
    ( 5, '李四', 19, '北京路29号', '男' ),
    ( 6, '张三', 18, '北京路18号', '男' ),
    ( 7, '王五', 19, '南京路11号', '女' ),
    ( 8, '马六', 18, '南京路19号', '女' );

    # 我们发现除了自增长ID不同以为，有几条其他字段都重复的数据出现，符合我们的需求。

找到重复的数据

  • 通过group by, 用Max函数或者Min函数均可，只是为了保证取出来的数据的唯一性。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT max(id) id,NAME,age,address,sex FROM person GROUP BY NAME,age,address,sex HAVING count(1)>1

    +----+------+-----+------------+-----+
    | id | NAME | age | address    | sex |
    +----+------+-----+------------+-----+
    |  6 | 张三 |  18 | 北京路18号 | 男  |
    |  5 | 李四 |  19 | 北京路29号 | 男  |
    |  7 | 王五 |  19 | 南京路11号 | 女  |
    +----+------+-----+------------+-----+
    3 rows in set (0.02 sec)

删除重复的数据

  • 其实我们数据库中最后要保留的结果就是上一步中查询出来的数据，我们把其他的数据删除即可。

  • 怎么删除呢？我们使用ID来排除。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    -- 将上面的数据作为临时表
    SELECT * FROM (SELECT MAX(ID) ID,NAME,Age,Address,Sex FROM Person GROUP BY NAME,Age,Address,Sex HAVING COUNT(1)> 1) T

    -- 将Peson和T表关联
    WHERE Person.NAME=T.NAME AND Person.Age=T.Age AND Person.Address=T.Address AND Person.Sex=T.Sex

    -- 根据id过滤数据 ：如果上面使用 MIN函数，这里就要改成 > ，取比max(id）小的数据
    AND Person.ID < T.ID

    -- 通过exist
    delete FROM Person WHERE EXISTS (
    SELECT * FROM (
    	SELECT MAX(ID) ID,NAME,Age,Address,Sex FROM Person GROUP BY NAME,Age,Address,Sex HAVING COUNT(1)> 1) T
        WHERE Person.NAME=T.NAME AND Person.Age=T.Age AND Person.Address=T.Address AND Person.Sex=T.Sex AND Person.ID < T.ID
    )

  • 错误的写法

    1
    2
    3
    -- 查询后直接通过id删除
    delete from person where id in ( select min(id) id from person group by name,age,address,sex having count(1)>1)
    > 1093 - You can't specify target table 'person' for update in FROM clause

  • 原因是：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作

  • 解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 将上面删除的重复数据做为一个第三方表
    -- select min(id) id from person group by name,age,address,sex having count(1)>1
    select t.id from (select min(id) id from person group by name,age,address,sex having count(1)>1) t

    -- 查出id最大的数据，这些数据就是我们要留下的，那么再查询出id不在这里面的，就是我们要删除的重复数据。

    -- 再次删除
    delete FROM person WHERE id in (
        select t.id from (select min(id) id from person group by name,age,address,sex having count(1)>1) t
    )