mysql数据库查重、去重
查重一：
|
0 |
SELECT COUNT(*),`name`,`data` FROM `setting` GROUP BY `name` HAVING count(*) > 1; |
结果：
查重二：
返回结果中的c字段是重复次数
|
0 |
SELECT *,COUNT(0) as c FROM `test1` GROUP BY `sex`; |
表：
查询结果：
查重三：
去重查找重复次数大于N的数据
|
0 |
SELECT *,COUNT(1) as c FROM `test1` GROUP BY `sex` HAVING c > 1; |
|
0 1 2 3 4 5 |
// 返回结果 +----+------+------+-----+---+ | id | name | wan | sex | c | +----+------+------+-----+---+ | 1 | 老大 | 20万 | 22 | 2 | +----+------+------+-----+---+ |
去重一：（删除多余的重复记录，只保留id最小的记录）
|
0 1 2 3 4 5 6 7 8 9 |
delete from user where nick_name in (select nick_name from (select nick_name from user group by nick_name having count(nick_name)>1) as tmp1) and id not in (select id from (select min(id) from user group by nick_name having count(nick_name)>1) as tmp2); |
去重二：（删除多余的重复记录，只保留id最小的记录）
这种方法好理解，还是使用这种方法的好。
|
0 1 2 3 4 5 |
DELETE FROM `test1` WHERE `id` NOT IN ( SELECT temp.min_id FROM ( SELECT MIN(id) AS min_id FROM `test1` GROUP BY `sex` ) AS temp ); |
实例截图：
