(一)单个字段
?
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测试数据
?
/* 表结构 */
DROP TABLE IF EXISTS `t1`;
CREATE TABLE IF NOT EXISTS `t1`(
? `id` INT(1) NOT NULL AUTO_INCREMENT,
? `name` VARCHAR(20) NOT NULL,
? `add` VARCHAR(20) NOT NULL,
? PRIMARY KEY(`id`)
)Engine=InnoDB;
?
/* 插入测试数据 */
INSERT INTO `t1`(`name`,`add`) VALUES
(‘abc‘,"123"),
(‘abc‘,"123"),
(‘abc‘,"321"),
(‘abc‘,"123"),
(‘xzy‘,"123"),
(‘xzy‘,"456"),
(‘xzy‘,"456"),
(‘xzy‘,"456"),
(‘xzy‘,"789"),
(‘xzy‘,"987"),
(‘xzy‘,"789"),
(‘ijk‘,"147"),
(‘ijk‘,"147"),
(‘ijk‘,"852"),
(‘opq‘,"852"),
(‘opq‘,"963"),
(‘opq‘,"741"),
(‘tpk‘,"741"),
(‘tpk‘,"963"),
(‘tpk‘,"963"),
(‘wer‘,"546"),
(‘wer‘,"546"),
(‘once‘,"546");
?
SELECT * FROM `t1`;
+----+------+-----+
| id | name | add |
+----+------+-----+
| ?1 | abc ?| 123 |
| ?2 | abc ?| 123 |
| ?3 | abc ?| 321 |
| ?4 | abc ?| 123 |
| ?5 | xzy ?| 123 |
| ?6 | xzy ?| 456 |
| ?7 | xzy ?| 456 |
| ?8 | xzy ?| 456 |
| ?9 | xzy ?| 789 |
| 10 | xzy ?| 987 |
| 11 | xzy ?| 789 |
| 12 | ijk ?| 147 |
| 13 | ijk ?| 147 |
| 14 | ijk ?| 852 |
| 15 | opq ?| 852 |
| 16 | opq ?| 963 |
| 17 | opq ?| 741 |
| 18 | tpk ?| 741 |
| 19 | tpk ?| 963 |
| 20 | tpk ?| 963 |
| 21 | wer ?| 546 |
| 22 | wer ?| 546 |
| 23 | once | 546 |
+----+------+-----+
rows in set (0.00 sec)
?
?
查找id最小的重复数据(只查找id字段)
?
?
/* 查找id最小的重复数据(只查找id字段) */
SELECT DISTINCT MIN(`id`) AS `id`
FROM `t1`
GROUP BY `name`,`add`
HAVING COUNT(1) > 1;
+------+
| id ? |
+------+
| ? ?1 |
| ? 12 |
| ? 19 |
| ? 21 |
| ? ?6 |
| ? ?9 |
+------+
rows in set (0.00 sec)
?
?
?
查找所有重复数据
?
/* 查找所有重复数据 */
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 ?| 123
| ?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)?
原文：http://shouce.iteye.com/blog/2265633