在日常业务场景中，经常会出现一个问题就是解决数据重复的问题，这里用到了一张用户表(s_user)做重复数据操作，分别包含了两个字段，id、name分别用于做唯一标示以及相同姓名的检索。

表结构以及测试数据

/*Navicat MySQL Data TransferSource Server         : 120.25.170.205Source Server Version : 50173Source Host           : 120.25.170.205:3306Source Database       : testTarget Server Type    : MYSQLTarget Server Version : 50173File Encoding         : 65001Date: 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_userwhere 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_userwhere 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_userwhere 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)