SQL删除重复数据只保留⼀条数据
1、表结构与数据：
CREATE TABLE tablezzl(
id int,
name VARCHAR(255)
);
mysql删除重复的数据保留一条
2、查询出重复的数据：
SELECT name FROM tablezzl GROUP BY name HAVING COUNT(name)>1
3、查询出要保留的重复数据：
SELECT MIN(id) ids FROM tablezzl GROUP BY NAME HAVING COUNT(name)>1
4、最终的SQL：
DELETE FROM tablezzl
WHERE1=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
WHERE1=1
AND `name` in (SELECT `name` `names` FROM tablezzl GROUP BY name HAVING COUNT(`nam
e`)>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包装⼀下就可以解决。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。