mysql同一张表查询并删除_MySQL查询表内重复记录并删除

在日常业务场景中，经常会出现一个问题就是解决数据重复的问题，这里用到了一张用户表(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)