在日常业务场景中，经常会出现一个问题就是解决数据重复的问题，这里用到了一张用户表(s_user)做重复数据操作，分别包含了两个字段，id、name分别用于做唯一标示以及相同姓名的检索。
表结构以及测试数据
/*
Navicat MySQL Data Transfer Source Server : 120.25.170.205
Source Server Version : 50173
Source Host : 120.25.170.205:3306
Source Database : test Target Server Type : MYSQL
Target Server Version : 50173
File Encoding : 65001 Date: 2018-08-13 17:11:24
*/ SET FOREIGN_KEY_CHECKS=0; -- ----------------------------
-- Table structure for s_user
-- ----------------------------
DROP TABLE IF EXISTS `s_user`;
CREATE TABLE `s_user` (
`id` varchar(36) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ----------------------------
-- Records of s_user
-- ----------------------------
INSERT INTO `s_user` VALUES ('a53cb32a-9ed0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('86badf60-9ea0-11e8-87f3-00163e064c72', '老王');
INSERT INTO `s_user` VALUES ('a50cb058-9ed0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('8529ce40-9ea0-11e8-87f3-00163e064c72', '老万');
INSERT INTO `s_user` VALUES ('a2d7d40c-9ed0-11e8-87f3-00163e064c72', '张三');
INSERT INTO `s_user` VALUES ('801528a0-9ea0-11e8-87f3-00163e064c72', '李四');
INSERT INTO `s_user` VALUES ('a313915e-9ed0-11e8-87f3-00163e064c72', '张三');
INSERT INTO `s_user` VALUES ('79a27d1a-9ea0-11e8-87f3-00163e064c72', '张三');
小葵花课堂开课了
1、如何通过用户名检索出相同的用户信息
select name,count(s.id) from s_user s GROUP BY s.`name` HAVING count(s.`name`)>1
2、通过用户名检索出所有相同的用户信息并删除
delete from s_user
where name in (SELECT * from(select s.name from s_user s group by s.name having count(name) > 1)s)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)
注意事项：由于子查询限制，如果子查询的 from 子句和更新、删除对象使用同一张表则需要在子查询外添加一个查询，否则会出现以下错误:
[Err] 1093 - You can't specify target table 's_user' for update in FROM clause
错误的查询：
delete from s_user
where name in (select s.name from s_user s group by s.name having count(name) > 1)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)
正确的查询：
delete from s_user
where name in (SELECT * from(select s.name from s_user s group by s.name having count(name) > 1)s)
and id not in (SELECT id from (select min(s.id)id from s_user s group by s.name having count(name)>1)a)