数据库重复记录筛选
查询f_name 重复的记录
select * from t_info a where ((select count(*) from t_info where f_name = a.f_name) > 1) order by f_name desc
或
select * from t_info where f_name in (select f_name from t_info group by f_name having count(*)>1)
查询f_name 重复的记录条数
select f_name, count(*) as number from t_info a where ((select count(*) from t_info where f_name = a.f_name) > 1) group by f_name
或
select f_name, count(*) as number from t_info where f_name in (select f_name from t_info group by f_name having count(*)>1) group by f_name
过滤重复记录(只显示一条,注:此处显示f_id最大一条记录)
select * from t_info where f_id in (select max(f_id) from t_info group by f_name)
删除全部重复记录(慎用)
delete t_info where f_name in (select f_name from t_info group by f_name having count(*)>1)
保留一条 (注:此处保留id最大一条记录)
delete t_info where f_id not in (select max(f_id) from t_info group by f_name)
在mysql中要用not in 删除需要按照下面的执行,不知为啥
delete t_info where f_id not in (select * from (select max(f_id) from t_info group by f_name))
作者:robert 出处:http://www.cnblogs.com/robertsun/ 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。 如果文中有错误,欢迎指出。以免更多的人被误导。 欢迎留言交流。 |