Mysql删除重复数据

最新推荐文章于 2023-08-17 11:18:27 发布

Terisadeng

最新推荐文章于 2023-08-17 11:18:27 发布

阅读量208

点赞数

分类专栏： Mysql 文章标签： Mysql

本文链接：https://blog.csdn.net/dongyuxu342719/article/details/77479832

版权

Mysql 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

测试表结构

CREATE TABLE `testdeleterepetition` (                                                                                                                                                                                                             
            `id` int(3) default NULL,                                                                                                                                                                                                                       
            `name` varchar(32) default NULL,                                                                                                                                                                                                                
            `hobby` varchar(32) default NULL,                                                                                                                                                                                                               
            `job` varchar(32) default NULL,                                                                                                                                                                                                                 
            `cc` varchar(2) default NULL                                                                                                                                                                                                                    
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          
CREATE TABLE `testdeleterepetition2` (                                                                                                         
                         `id` int(3) default NULL,                                                                                                                    
                         `extend` varchar(32) default NULL                                                                                                            
                       ) ENGINE=InnoDB DEFAULT CHARSET=latin1

表testdeleterepetition中的数据

insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('1','steve','11','22','33');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('2','ali','22','33','44');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('3','ali','22','33','44');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('4','cs','12','32','43');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('5','cs','12','32','43');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('6','cs','12','32','43');
insert into `testdeleterepetition` (`id`, `name`, `hobby`, `job`, `cc`) values('7','aa','fe','12','dv');

表testdeleterepetition2中的数据

insert into `testdeleterepetition2` (`id`, `extend`) values('1','1');
insert into `testdeleterepetition2` (`id`, `extend`) values('2','1');
insert into `testdeleterepetition2` (`id`, `extend`) values('3','1');
insert into `testdeleterepetition2` (`id`, `extend`) values('4','2');
insert into `testdeleterepetition2` (`id`, `extend`) values('5','2');
insert into `testdeleterepetition2` (`id`, `extend`) values('6','2');
insert into `testdeleterepetition2` (`id`, `extend`) values('7','2');

删除testdeleterepetition中name、hobby、job、cc字段重复的列，保留id最小的列

DELETE FROM testdeleterepetition 
WHERE id NOT IN (SELECT t.minid FROM (SELECT MIN(id) minid FROM `testdeleterepetition`
GROUP BY NAME,hobby,job) t);

删除testdeleterepetition表中的重复数据，保留最小id，且和testdeleterepetition2表通过id字段关联，取testdeleterepetition2表extend字段为1为限制条件

DELETE testdeleterepetition a
FROM testdeleterepetition a
INNER JOIN testdeleterepetition2 b
ON a.`id`=b.`id`
WHERE a.id NOT IN (SELECT t.minid FROM (SELECT MIN(id) minid FROM `testdeleterepetition`
GROUP BY NAME,hobby,job) t)
AND b.extend=1;