Mysql删除重复数据

青旅半醒 2022-06-08 23:29 172阅读 0赞

测试表结构

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;