表结构

CREATE TABLE `people` (`id` int(11) NOT NULL AUTO_INCREMENT,`peopleName` varchar(2000) DEFAULT NULL COMMENT 'peopleName',`status` tinyint(1) DEFAULT NULL COMMENT '0无效 1有效',`seq` int(5) NOT NULL DEFAULT '1' COMMENT '记录值',`peopleId` tinyint(4) DEFAULT '0' COMMENT 'peopleId',`create_time` datetime DEFAULT CURRENT_TIMESTAMP,`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1133 DEFAULT CHARSET=utf8 COMMENT='people测试表';

删除重复值

1. 查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2. 删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有peopleId最小的记录

delete from people
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

3. 查找表中多余的重复记录（多个字段）

select * from people a
where (a.peopleId,a.seq) in (select peopleId,seq from people group by peopleId,seq having count(*) > 1)

4. 删除表中多余的重复记录（多个字段），只留有id最小的记录

delete from people a
where (a.peopleId,a.seq) in (select peopleId,seq from people group by peopleId,seq having count(*) > 1)
and id not in (select min(id) from people group by peopleId,seq having count(*)>1)

5. 查找表中多余的重复记录（多个字段），不包含id最小的记录

select * from people a
where (a.peopleId,a.seq) in (select peopleId,seq from people group by peopleId,seq having count(*) > 1)
and id not in (select min(id) from people group by peopleId,seq having count(*)>1)   

记录使用过程中出现的错误

在使用mysql5.7时，发现存在如下的报错

You can't specify target table for update in FROM clause
含义:不能在同一表中查询的数据作为同一表的更新数据。

解决方法

-- 以以下的SQL举例
delete from people
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)-- 修改为
delete from people
where   peopleName in （select a.peopleName from (select peopleName    from people group by peopleName      having count(peopleName) > 1) a) 
and   peopleId not in (select b.peopleName from (select min(peopleId) from people group by peopleName     having count(peopleName)>1) b)

参考
[1]https://blog.csdn.net/u012767761/article/details/84997962
[2]https://blog.csdn.net/anya/article/details/6407280