Mysql中查找並刪除重復數據的方法

 　　(一)單個字段

　　1、查找表中多余的重復記錄，根據(question_title)字段來判斷

 代碼如下   select * from questions where question_title in (select question_title from peoplegroup 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 |