数据重复筛选 MYSQL_数据库重复记录筛选

查询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))