有一个表user，字段分别有id、nick_name、password、email、phone。

一、单字段（nick_name）

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

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

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

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);

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);

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

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); 

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

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);

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);

更多相关文章

1. mybatisplus的坑 insert标签insert into select无参数问题的解决
2. python起点网月票榜字体反爬案例
3. 《Android开发从零开始》——25.数据存储(4)
4. Android系统配置数据库注释(settings.db)
5. Android中不同应用间实现SharedPreferences数据共享
6. android图表ichartjs
7. Android内容提供者源码
8. android SharedPreferences
9. Android(安卓)Paging组件Demo

随机推荐

1. Python学习之路：函数的非固定参数
2. python编程从入门到实战1-3章
3. 简谈-如何将图片下载到本地
4. [D]用python提取多段字符串该怎么写正则
5. Python实现快速傅里叶变换（FFT）
6. Python出现Could not find a version tha
7. python之选课系统详解[功能未完善]
8. Python是如何进行内存管理的
9. Python内置函数介绍
10. 如何从单一路径获取上层路径？