MYSQL数据库的查重去重

MYSQL数据库的查重去重过程如下:

1. 查询用户表的全部数据:

select * from user;

id user_id user_name tel email
1 001 张三 13811112222 [email protected]
2 002 李四 13811112222 [email protected]
3 003 王五 13833333333 [email protected]

 

2. 查询重复注册手机号次数大于1的用户 :

select tel,count(*) regNum from user group by tel having regNum>1;

tel regNum
13811112222 2

 

3. 查询用户表的手机号,并去除重复:

a) select distinct tel from user;

b) select tel from user group by tel;

tel
13811112222
13833333333

 

4. 查询每个电话被注册的最大id,及对应的电话

select max(id),tel from user group by tel;

max(id) tel
3 13833333333
2 13811112222

 

5. 查询每个电话被注册的最大id表

select id from (select max(id) as 'id',tel from user group by tel) as id_table;

id
3
2

 

6. 查询每个电话被注册的最大id的详细记录

select * from user where id in (select id from (select max(id) as 'id',tel from user group by tel) id_table);

id user_id user_name tel email
2 002 李四 13811112222 [email protected]
3 003 王五 13833333333 [email protected]

 

7. 删除重复记录,保留每个电话的最大id的那条记录 

delete from user where id not in (select id from (select max(id) as 'id',tel from user group by tel) id_table);

id user_id user_name tel email
2 002 李四 13811112222 [email protected]
3 003 王五 13833333333 [email protected]
浏览:384 | 点赞:197 | 评论:0
全部评论
暂无评论

快速评论