首页 > 数据库技术 > 详细

mysql 去重

时间:2020-09-16 14:46:06      阅读:73      评论:0      收藏:0      [点我收藏+]

https://blog.csdn.net/eagle89/article/details/90901755

 

假设有一个表user,字段分别有id–nick_name–password–email–phone,分情况如下(注意删除多余记录时要创建临时表,不然会报错):

一、单字段(nick_name)

1、查出所有有重复记录的所有记录

select * from user where nick_name in

     (select nick_name from user group by nick_name having count(nick_name)>1);

2、查出有重复记录的各个记录组中id最大的记录

select * from user where id in (select max(id) from user group by nick_name having count(nick_name)>1);

3、查出多余的记录,不查出id最小的记录

select * from user where nick_name in

     (select nick_name from user group by nick_name having count(nick_name)>1)

and id not in

     (select min(id) from user group by nick_name having count(nick_name)>1);

4、删除多余的重复记录,只保留id最小的记录

delete from user where nick_name in

     (select nick_name from

          (select nick_name from user group by nick_name having count(nick_name)>1) as tmp1)

and id not in

      (select id from

          (select min(id) from user group by nick_name having count(nick_name)>1) as tmp2);

二、多字段(nick_name,password)

1、查出所有有重复记录的记录

select * from user where (nick_name,password) in

     (select nick_name,password from user group by nick_name,password where having count(nick_name)>1);

2、查出有重复记录的各个记录组中id最大的记录

select * from user where id in

     (select max(id) from user group by nick_name,password where having count(nick_name)>1);

3、查出各个重复记录组中多余的记录数据,不查出id最小的一条

select * from user where (nick_name,password) in

     (select nick_name,password from user group by nick_name,password having count(nick_name)>1)

and id not in

     (select min(id) from user group by nick_name,password having count(nick_name)>1);

4、删除多余的重复记录,只保留id最小的记录

delete from user where (nick_name,password) in

     (select nick_name,password from

          (select nick_name,password from user group by nick_name,password having count(nick_name)>1) as tmp1)

and id not in

     (select id from

          (select min(id) id from user group by nick_name,password having count(nick_name)>1) as tmp2);

mysql 去重

原文:https://www.cnblogs.com/zonglonglong/p/13678504.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!