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

创建多条数据

---

1 创建表

CREATE TABLE table_name(
id int,
name VARCHAR(50)
);

2 查询出重复数据

SELECT name FROM table_name GROUP BY name HAVING COUNT(name)>1
多条件
select * from table_name a
where (a.name,a.age) in (select name,age from table_name group by name,age having count(*) > 1)

3 删除重复数据

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

4 补充一下

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) 

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