SQL删除重复数据只保留一条数据

时间：2022-09-22 22:42:14

1、表结构与数据：

CREATE TABLE tablezzl(
id int,
name VARCHAR(255)
);

SQL删除重复数据只保留一条数据

2、查询出重复的数据：

SELECT name FROM tablezzl GROUP BY name HAVING COUNT(name)>1

SQL删除重复数据只保留一条数据

3、查询出要保留的重复数据：

SELECT MIN(id) ids FROM tablezzl GROUP BY NAME HAVING COUNT(name)>1

SQL删除重复数据只保留一条数据

4、最终的SQL：

DELETE FROM tablezzl 
WHERE 1=1
    AND `name` in (SELECT * FROM ( (SELECT `name` `names` FROM tablezzl GROUP BY name HAVING COUNT(`name`)>1) ) a)
    AND id not in (SELECT * FROM ( (SELECT MIN(id) ids FROM tablezzl GROUP BY NAME HAVING COUNT(`name`)>1) ) b)

5、补充 ：　

　　其中这样写mysql中不能如下这样写：

DELETE FROM tablezzl 
WHERE 1=1
AND `name` in (SELECT `name` `names` FROM tablezzl GROUP BY name HAVING COUNT(`name`)>1) 
AND id not in (SELECT MIN(id) ids FROM tablezzl GROUP BY NAME HAVING COUNT(`name`)>1)

会报错：You can't specify target table 'tablezzl' for update in FROM clause，不能在同一表中查询的数据作为同一表的更新数据，如模块4的SQL包装一下就可以解决。

 

标签：

相关文章