MySQL查找重复记录并根据条件字段进行删除
方法/步骤
1
使用sql语句查询测试表的数据如下图:SELECT * from ceshi
2
目的是找出ip相同的记录，并删除其中rq较小的那个。先通过各场包分组group，找出重复的ipSELECT ip from ceshi GROUP BY ip HAVING count(ip)>1注意：mysql的分组group和oracle不同，oracle在查询字段出来的字段必须在group by 后有，mysql不需要
3
查找狠腊相同 ip 记录 rq 字段较小的记录SELECT min(rq),id from ceshi GROUP BY ip HAVING count(ip)>1
5
直接用这个语句会报错。[Err] 1248 - Every derived table must have its own alias这个错误是因为表没有别名
6
修改语句如下：select id from (SELECT min(rq),id from ceshi GROUP BY ip HAVING count(ip)>1 ) a给子查询结果集表取名a 再执行就正常了
7
根据 id 删除重复 ip 中 rq 较小的那条记录DELETE from ceshi where id in (select id from (SELECT min(rq),id from ceshi GROUP BY ip HAVING count(ip)>1 ) a)这个语句难点在于别名，在oracle中不会这样