(一)单个字段
1、查找表中多余的重复记录,根据(question_title)字段来判断
代码如下 | 复制代码 |
select * from questions where question_title in (select question_title from people group by question_title having count(question_title) > 1) |
2、删除表中多余的重复记录,根据(question_title)字段来判断,只留有一个记录
代码如下 | 复制代码 |
delete from questions where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1) and min(id) not in (select question_id from questions group by question_title having count(question_title)>1) |
(二)多个字段
删除表中多余的重复记录(多个字段),只留有rowid最小的记录
代码如下 | 复制代码 |
DELETE FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1) |
用上述语句无法删除,创建了临时表才删的,求各位达人解释一下。
代码如下 | 复制代码 |
CREATE TABLE tmp AS SELECT question_id FROM questions WHERE (questions_title,questions_scope) IN (SELECT questions_title,questions_scope FROM questions GROUP BY questions_title,questions_scope HAVING COUNT(*) > 1) AND question_id NOT IN (SELECT MIN(question_id) FROM questions GROUP BY questions_scope,questions_title HAVING COUNT(*)>1); DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp); DROP TABLE tmp; |
(三) 存储过程
代码如下 | 复制代码 |
declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 |
例,
数据库版本 Server version: 5.1.41-community-log MySQL Community Server (GPL)
例1,表中有主键(可唯一标识的字段),且该字段为数字类型
例1测试数据
代码如下 | 复制代码 |
/* 表结构 */ /* 插入测试数据 */ SELECT * FROM `t1`; |
查找id最小的重复数据(只查找id字段)
代码如下 | 复制代码 |
/* 查找id最小的重复数据(只查找id字段) */ |
查找所有重复数据
代码如下 | 复制代码 |
/* 查找所有重复数据 */ SELECT `t1`.* FROM `t1`,( SELECT `name`,`add` FROM `t1` GROUP BY `name`,`add` HAVING COUNT(1) > 1 ) AS `t2` WHERE `t1`.`name` = `t2`.`name` AND `t1`.`add` = `t2`.`add`; +----+------+-----+ |
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 4 | abc | www.111com.net|
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
+----+------+-----+
rows in set (0.00 sec)