sql语句，如何找出重复的数据及如何删除

案例： 在user 表中存在着上万条记录，且存在着很多手机号mobile 相同的数据，那么如何筛选出这些相同的数据呢？

1、查找表中多余的重复记录

select * from user

where mobile in (select  mobile  from  user  group  by  mobile  having  count(mobile) > 1) 

order by mobile;

2、删除表中多余的重复记录，只留有id最小的记录

delete from user

where mobile  in (select  mobile  from user  group  by  mobile  having  count(mobile) > 1)

and id not in (select min(id) from  user  group by mobile  having count(mobile )>1)