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

最新推荐文章于 2024-08-28 08:50:29 发布

weixin_33971977

最新推荐文章于 2024-08-28 08:50:29 发布

阅读量58

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/sqy123/p/10038559.html

版权

建表语句

CREATE TABLE `student` (
    `id` BIGINT (20),
    `s_name` VARCHAR (765),
    `age` INT (2)
); 
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('100','花','15');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('111','明','14');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('112','明','12');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('122','张','10');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('123','明','13');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('124','明','11');
INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('126','花','11');

在网上查到很多关于这道题的答案，但很多都是错的，比如

delete from student 
where s_name in (select s_name from student group by s_name having count(s_name) > 1) 
and id not in (select min(id) from people group by s_name having count(s_name)>1)

这句话在MySQL里执行会报：

You can't specify target table 'student' for update in FROM clause

意思就是不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

解决方案就是用通过中间表来规避这个错误，sql语句如下：

DELETE 
FROM
  student 
WHERE id NOT IN 
  (SELECT 
    a.id 
  FROM
    (SELECT 
      MIN(id) AS id 
    FROM
      student 
    GROUP BY s_name 
    HAVING COUNT(s_name) > 1) AS a)

但是这样写会有个问题，会把s_name没有重复的数据也删掉例如：

INSERT INTO `student` (`id`, `s_name`, `age`) VALUES('122','张','10');这条数据

很多答案也没有考虑到这个问题，把s_name没有重复的数据排除掉就行了。

最后形成的语句如下：

DELETE 
FROM
  student 
WHERE id NOT IN 
  (SELECT 
    a.id 
  FROM
    (SELECT 
      MIN(id) AS id 
    FROM
      student 
    GROUP BY s_name 
    HAVING COUNT(s_name) > 1) AS a) 
  AND s_name IN 
  (SELECT 
    b.s_name 
  FROM
    (SELECT 
      s_name 
    FROM
      student 
    GROUP BY s_name 
    HAVING COUNT(s_name) > 1) AS b)

转载于:https://www.cnblogs.com/sqy123/p/10038559.html