mysql数据库如何删除重复记录(mysql数据库删除重复数据只保留一条方法实例)
mysql数据库如何删除重复记录
mysql数据库删除重复数据只保留一条方法实例1.问题引入
假设一个场景,一张用户表,包含3个字段。id,identity_id,name。现在身份证号identity_id和姓名name有很多重复的数据,需要删除只保留一条有效数据。
2.模拟环境
1.登入mysql数据库,创建一个单独的测试数据库mysql_exercise
|
create database mysql_exercise charset utf8; |
2.创建用户表users
|
create table users( id int auto_increment primary key , identity_id varchar (20), name varchar (20) not null ); |
3.插入测试数据
|
insert into users values (0, '620616199409206512' , '张三' ), (0, '620616199409206512' , '张三' ), (0, '62062619930920651x' , '李四' ), (0, '62062619930920651x' , '李四' ), (0, '620622199101206211' , '王五' ), (0, '620622199101206211' , '王五' ), (0, '322235199909116233' , '赵六' ); |
可以多执行几次,生成较多重复数据。
4.解决思路
(1)根据身份证号和name进行分组;
(2)取出分组后的最大id(或最小id);
(3)删除除最大(或最小)id以外的其他字段;
5.第一次尝试(失败!!!)
|
delete from users where id not in ( select max (id) from users group by identity_id, name ); |
报错:
1093 (hy000): you can't specify target table 'users' for update in from clause
因为在mysql里,不能先select一个表的记录,再按此条件进行更新和删除同一个表的记录。
解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误,
这个问题只出现于mysql,mssql和oracle不会出现此问题。
所以我们可以先将括号里面的sql语句先拿出来,先查到最大(或最小)id。
|
select max_id from ( select max (id) as max_id from users group by identity_id, name ); |
接着,又报错了!!!
error 1248 (42000): every derived table must have its own alias
意思是说:提示说每一个衍生出来的表,必须要有自己的别名!
执行子查询的时候,外层查询会将内层的查询当做一张表来处理,所以我们需要给内层的查询加上别名
继续更正:
给查询到的最大(或最小id)结果当做一张新的表,起别名t,并查询t.mix_id。
|
select t.max_id from ( select max (id) as max_id from users group by identity_id, name ) as t; |
可以成功查到最大(或最小)id了,如下图:
6.第二次尝试(成功!!!)
|
delete from users where id not in ( select t.max_id from ( select max (id) as max_id from users group by identity_id, name ) as t ); |
执行结果:
成功将重复的数据删除,只保留了最后一次增加的记录。同理也可以保留第一次添加的记录(即删除每个分组里面除最小id以外的其他条记录)
3.知识拓展一:更新数据
其他场景应用:要将用户表user_info里名字(name)为空字符串("")的用户的状态(status)改成"0"
|
update user_info set status= '0' where user_id in ( select user_id from user_info where name = '' ) |
同样报了如下错误:
you can't specify target table ‘user_info' for update in from clause
因为在mysql里,不能先select一个表的记录,再按此条件进行更新和删除同一个表的记录,解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误。
以下两种均可!!!
|
update user_info set status= '0' where user_id in ( select user_id from ( select user_id from user_info where name = '' ) t1); |
下面这种也可,细微差别,别名可带as可不带,t1.user_id 直接和内层的user_id对应也可以。
|
update user_info set status= '0' where user_id in ( select t1.user_id from ( select user_id from user_info where name = '' ) as t1); |
3.1 分步骤解析
(1)将以下查询结果作为中间表:
|
select user_id from user_info where name = '' ; |
(2)再查询一遍中间表作为结果集:
|
select user_id from ( select user_id from user_info where name = '' ) as t; |
(3)更新数据
|
update user_info set status= '0' where user_id in ( select user_id from ( select user_id from user_info where name = '' ) as t1); |
4.拓展练习:删除重复数据
编写一个 sql 查询,来删除 person 表中所有重复的电子邮箱,重复的邮箱里只保留 id 最小 的那个。
|
+ ----+------------------+ | id | email | + ----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | + ----+------------------+ |
id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 person 表应返回以下几行:
|
+ ----+------------------+ | id | email | + ----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | + ----+------------------+ |
解答一:
|
delete from person where id not in ( select t.min_id from ( select min (id) as min_id from person group by email ) as t ); |
解答二:
|
delete p1 from person as p1,person as p2 where p1.email=p2.email and p1.id > p2.id; |
总结
到此这篇关于mysql数据库删除重复数据的方法只保留一条的文章就介绍到这了,更多相关mysql删除重复数据内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/qq_38923792/article/details/95240733
- mysql千万级别数据查询优化(mysql千万级数据量根据索引优化查询速度的实现)
- mysql 8.0.22 winx64安装配置方法图文教程(mysql 8.0.22 winx64安装配置方法图文教程)
- mysql存储引擎是什么(详解mysql中的存储引擎)
- mysqlgroupby语句实现原理(Mysql中错误使用SQL语句Groupby被兼容的情况)
- mysql必背知识点高级(MySQL 8.0 Online DDL快速加列的相关总结)
- mysql数据库的备份与恢复的方法(详解Mysql之mysqlbackup备份与恢复实践)
- mysql是自动commit吗(详解MySQL与Spring的自动提交autocommit)
- mysql 高效分页(MySQL 分页查询的优化技巧)
- navicat连接mysql是远程连接吗(详解Navicat远程连接mysql很慢)
- mysqlreplace用法(细说mysql replace into用法)
- mysql 5.5.27 winx64安装配置方法图文教程(mysql 5.5.27 winx64安装配置方法图文教程)
- mysql数据库调优技术大全(Mysql数据库性能优化三分表、增量备份、还原)
- navicat连接mysql1045解决方法(Navicat 连接服务器端中的docker数据库的方法)
- mysql8.0.23.0官方安装手册(MySQL8.0.23安装超详细教程)
- 阿里云的自带mysql怎么开启(阿里云云服务器mysql密码找回的方法)
- mysql锁技术(MySQL 加锁控制并发的方法)
- 苏志燮赵恩静结婚,韩国四大公共财产变三人,这么快就有替补了(苏志燮赵恩静结婚)
- 《内在美》后,一大波新韩剧来袭,李钟硕朴信惠宋慧乔玄彬回归(一大波新韩剧来袭)
- 给孩子选购保温杯,注意这4个步骤,比颜值更重要(给孩子选购保温杯)
- 保温好 容量大 颜值高 保温杯你给娃娃买对了吗(保温好容量大颜值高)
- 《道德经》 人生避开骄狂,才能免去祸患(道德经人生避开骄狂)
- 郭麒麟(郭麒麟)
热门推荐
- ftp总是出现错误(FTP出现500 OOPS: cannot change directory的解决方法)
- mysqlupdate语句用法(MySQL update set 和 and的区别)
- dede菜单激活状态(DEDE采集大师官方留后门的删除办法)
- webmaven项目如何启动(js基础语法与maven项目配置教程案例)
- dede优化教程(DEDE调用分类及分类下文章并限制标题字数及显示条数)
- cssdiv边框外发光怎么设置(DIV+CSS实现带三角箭头的提示框)
- css 最高层级(浅谈CSS 权值 层叠 重要性!important)
- yield函数详解(Yii框架的redis命令使用方法简单示例)
- windows下nginx 命令(win10安装配置nginx的过程)
- sqlserver并发性能(sql server中的任务调度与CPU深入讲解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9