在數(shù)據(jù)庫(kù)中我們經(jīng)常會(huì)碰到重復(fù)數(shù)據(jù)了,下面我來(lái)介紹利用相關(guān)的mysql語(yǔ)句實(shí)現(xiàn)查找重復(fù)記錄并且實(shí)現(xiàn)刪除重復(fù)記錄的sql語(yǔ)句.

考試系統(tǒng)中做了一個(gè)用戶導(dǎo)入試題功能,導(dǎo)致用戶導(dǎo)入了很多重復(fù)的試題,我需要查詢及刪除一下重復(fù)的記錄,于是有了這篇文章.

(一)單個(gè)字段

1、查找表中多余的重復(fù)記錄,根據(jù)（question_title）字段來(lái)判斷,代碼如下:

select * from questions where question_title in (select question_title from people group by question_title having count(question_title) > 1)

2、刪除表中多余的重復(fù)記錄,根據(jù)(question_title)字段來(lái)判斷,只留有一個(gè)記錄,代碼如下:

1. delete from questions 
2. where peopleId in (select peopleId from people group by peopleId having count(question_title) > 1) 
3. and min(id) not in (select question_id from questions group by question_title having count(question_title)>1) 

(二)多個(gè)字段

刪除表中多余的重復(fù)記錄(多個(gè)字段),只留有rowid最小的記錄,代碼如下:

1. 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) 

用上述語(yǔ)句無(wú)法刪除,創(chuàng)建了臨時(shí)表才刪的,求各位達(dá)人解釋一下,代碼如下:

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); 
2. DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp); 
3. DROP TABLE tmp; 

(三),代碼如下:

1. declare @max integer,@id integer 
2. declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 
3. open cur_rows 
4. fetch cur_rows into @id,@max 
5. while @@fetch_status=0 
6. begin 
7. select @max = @max -1 
8. set rowcount @max 
9. delete from 表名 where 主字段 = @id 
10. fetch cur_rows into @id,@max 
11. end 
12. close cur_rows 
13. set rowcount 0 

上面講到了很多,下面我們一起來(lái)看實(shí)例刪除重復(fù)記錄實(shí)例.

例1,表中有主鍵(可唯一標(biāo)識(shí)的字段),且該字段為數(shù)字類型,代碼如下:

1. /* 表結(jié)構(gòu) */ 
2. DROP TABLE IF EXISTS `t1`; 
3. CREATE TABLE IF NOT EXISTS `t1`( 
4.   `id` INT(1) NOT NULL AUTO_INCREMENT, 
5.   `name` VARCHAR(20) NOT NULL, 
6.   `add` VARCHAR(20) NOT NULL, 
7.   PRIMARY KEY(`id`) 
8. )Engine=InnoDB; 
9. /* 插入測(cè)試數(shù)據(jù) */ 
10. INSERT INTO `t1`(`name`,`add`) VALUES 
11. ('abc',"123"), 
12. ('abc',"123"), 
13. ('abc',"321"), 
14. ('abc',"123"), 
15. ('xzy',"123"), 
16. ('xzy',"456"), 
17. ('xzy',"456"), 
18. ('xzy',"456"), 
19. ('xzy',"789"), 
20. ('xzy',"987"), 
21. ('xzy',"789"), 
22. ('ijk',"147"), 
23. ('ijk',"147"), 
24. ('ijk',"852"), 
25. ('opq',"852"), 
26. ('opq',"963"), 
27. ('opq',"741"), 
28. ('tpk',"741"), 
29. ('tpk',"963"), 
30. ('tpk',"963"), 
31. ('wer',"546"), 
32. ('wer',"546"), 
33. ('once',"546"); 
34. SELECT * FROM `t1`; 
35. +----+------+-----+ 
36. | id | name | add | 
37. +----+------+-----+ 
38. |  1 | abc  | 123 | 
39. |  2 | abc  | 123 | 
40. |  3 | abc  | 321 | 
41. |  4 | abc  | 123 | 
42. |  5 | xzy  | 123 | 
43. |  6 | xzy  | 456 | 
44. |  7 | xzy  | 456 | 
45. |  8 | xzy  | 456 | 
46. |  9 | xzy  | 789 | 
47. | 10 | xzy  | 987 | 
48. | 11 | xzy  | 789 | 
49. | 12 | ijk  | 147 | 
50. | 13 | ijk  | 147 | 
51. | 14 | ijk  | 852 | 
52. | 15 | opq  | 852 | 
53. | 16 | opq  | 963 | 
54. | 17 | opq  | 741 | 
55. | 18 | tpk  | 741 | 
56. | 19 | tpk  | 963 | 
57. | 20 | tpk  | 963 | 
58. | 21 | wer  | 546 | 
59. | 22 | wer  | 546 | 
60. | 23 | once | 546 | 
61. +----+------+-----+ 
62. rows in set (0.00 sec) 

查找id最小的重復(fù)數(shù)據(jù)(只查找id字段),代碼如下:

1. /* 查找id最小的重復(fù)數(shù)據(jù)(只查找id字段) */ 
2. SELECT DISTINCT MIN(`id`) AS `id` 
3. FROM `t1` 
4. GROUP BY `name`,`add` 
5. HAVING COUNT(1) > 1; 
6. +------+ 
7. | id   | 
8. +------+ 
9. |    1 | 
10. |   12 | 
11. |   19 | 
12. |   21 | 
13. |    6 | 
14. |    9 | 
15. +------+ 
16. rows in set (0.00 sec) 

查找所有重復(fù)數(shù)據(jù),代碼如下:

1. /* 查找所有重復(fù)數(shù)據(jù) */ 
2. SELECT `t1`.* 
3. FROM `t1`,( 
4.   SELECT `name`,`add` 
5.   FROM `t1` 
6.   GROUP BY `name`,`add` 
7.   HAVING COUNT(1) > 1 
8. ) AS `t2` 
9. WHERE `t1`.`name` = `t2`.`name` 
10.   AND `t1`.`add` = `t2`.`add`; 
11. +----+------+-----+ 
12. | id | name | add | 
13. +----+------+-----+ 
14. |  1 | abc  | 123 | 
15. |  2 | abc  | 123 | 
16. |  4 | abc  | 123 | 
17. |  6 | xzy  | 456 | 
18. |  7 | xzy  | 456 | 
19. |  8 | xzy  | 456 | 
20. |  9 | xzy  | 789 | 
21. | 11 | xzy  | 789 | 
22. | 12 | ijk  | 147 | 
23. | 13 | ijk  | 147 | 
24. | 19 | tpk  | 963 | 
25. | 20 | tpk  | 963 | 
26. | 21 | wer  | 546 | 
27. | 22 | wer  | 546 | 
28. +----+------+-----+ 
29. rows in set (0.00 sec) 

查找除id最小的數(shù)據(jù)外的重復(fù)數(shù)據(jù),代碼如下:

1. /* 查找除id最小的數(shù)據(jù)外的重復(fù)數(shù)據(jù) */ 
2. SELECT `t1`.* 
3. FROM `t1`,( 
4.   SELECT DISTINCT MIN(`id`) AS `id`,`name`,`add` 
5.   FROM `t1` 
6.   GROUP BY `name`,`add` 
7.   HAVING COUNT(1) > 1 
8. ) AS `t2` 
9. WHERE `t1`.`name` = `t2`.`name` 
10.   AND `t1`.`add` = `t2`.`add` 
11.   AND `t1`.`id` <> `t2`.`id`; 
12. +----+------+-----+ 
13. | id | name | add | 
14. +----+------+-----+ 
15. |  2 | abc  | 123 | 
16. |  4 | abc  | 123 | 
17. |  7 | xzy  | 456 | 
18. |  8 | xzy  | 456 | 
19. | 11 | xzy  | 789 | 
20. | 13 | ijk  | 147 | 
21. | 20 | tpk  | 963 | 
22. | 22 | wer  | 546 | 
23. +----+------+-----+ 
24. rows in set (0.00 sec) 

例2,表中沒(méi)有主鍵(可唯一標(biāo)識(shí)的字段),或者主鍵并非數(shù)字類型(也可以刪除重復(fù)數(shù)據(jù),但效率上肯定比較慢.

例2測(cè)試數(shù)據(jù),代碼如下:

1. /* 表結(jié)構(gòu) */ 
2. DROP TABLE IF EXISTS `noid`; 
3. CREATE TABLE IF NOT EXISTS `noid`( 
4.   `pk` VARCHAR(20) NOT NULL COMMENT '字符串主鍵', 
5.   `name` VARCHAR(20) NOT NULL, 
6.   `add` VARCHAR(20) NOT NULL, 
7.   PRIMARY KEY(`pk`) 
8. )Engine=InnoDB; 
9. /* 測(cè)試數(shù)據(jù)，與上例一樣的測(cè)試數(shù)據(jù)，只是主鍵變?yōu)樽址问?nbsp;*/ 
10. INSERT INTO `noid`(`pk`,`name`,`add`) VALUES 
11. ('a','abc',"123"), 
12. ('b','abc',"123"), 
13. ('c','abc',"321"), 
14. ('d','abc',"123"), 
15. ('e','xzy',"123"), 
16. ('f','xzy',"456"), 
17. ('g','xzy',"456"), 
18. ('h','xzy',"456"), 
19. ('i','xzy',"789"), 
20. ('j','xzy',"987"), 
21. ('k','xzy',"789"), 
22. ('l','ijk',"147"), 
23. ('m','ijk',"147"), 
24. ('n','ijk',"852"), 
25. ('o','opq',"852"), 
26. ('p','opq',"963"), 
27. ('q','opq',"741"), 
28. ('r','tpk',"741"), 
29. ('s','tpk',"963"), 
30. ('t','tpk',"963"), 
31. ('u','wer',"546"), 
32. ('v','wer',"546"), 
33. ('w','once',"546"); 
34. SELECT * FROM `noid`; 
35. +----+------+-----+ 
36. | pk | name | add | 
37. +----+------+-----+ 
38. | a  | abc  | 123 | 
39. | b  | abc  | 123 | 
40. | c  | abc  | 321 | 
41. | d  | abc  | 123 | 
42. | e  | xzy  | 123 | 
43. | f  | xzy  | 456 | 
44. | g  | xzy  | 456 | 
45. | h  | xzy  | 456 | 
46. | i  | xzy  | 789 | 
47. | j  | xzy  | 987 | 
48. | k  | xzy  | 789 | 
49. | l  | ijk  | 147 | 
50. | m  | ijk  | 147 | 
51. | n  | ijk  | 852 | 
52. | o  | opq  | 852 | 
53. | p  | opq  | 963 | 
54. | q  | opq  | 741 | 
55. | r  | tpk  | 741 | 
56. | s  | tpk  | 963 | 
57. | t  | tpk  | 963 | 
58. | u  | wer  | 546 | 
59. | v  | wer  | 546 | 
60. | w  | once | 546 | 
61. +----+------+-----+ 
62. rows in set (0.00 sec) 

為表添加自增長(zhǎng)的id字段,代碼如下:

1. /* 為表添加自增長(zhǎng)的id字段 */ 
2. ALTER TABLE `noid` ADD `id` INT(1) NOT NULL AUTO_INCREMENT, ADD INDEX `id`(`id`); 
3. Query OK, 23 rows affected (0.16 sec) 
4. Records: 23  Duplicates: 0  Warnings: 0 
5. SELECT * FROM `noid`; 
6. +----+------+-----+----+ 
7. | pk | name | add | id | 
8. +----+------+-----+----+ 
9. | a  | abc  | 123 |  1 | 
10. | b  | abc  | 123 |  2 | 
11. | c  | abc  | 321 |  3 | 
12. | d  | abc  | 123 |  4 | 
13. | e  | xzy  | 123 |  5 | 
14. | f  | xzy  | 456 |  6 | 
15. | g  | xzy  | 456 |  7 | 
16. | h  | xzy  | 456 |  8 | 
17. | i  | xzy  | 789 |  9 | 
18. | j  | xzy  | 987 | 10 | 
19. | k  | xzy  | 789 | 11 | 
20. | l  | ijk  | 147 | 12 | 
21. | m  | ijk  | 147 | 13 | 
22. | n  | ijk  | 852 | 14 | 
23. | o  | opq  | 852 | 15 | 
24. | p  | opq  | 963 | 16 | 
25. | q  | opq  | 741 | 17 | 
26. | r  | tpk  | 741 | 18 | 
27. | s  | tpk  | 963 | 19 | 
28. | t  | tpk  | 963 | 20 | 
29. | u  | wer  | 546 | 21 | 
30. | v  | wer  | 546 | 22 | 
31. | w  | once | 546 | 23 | 
32. +----+------+-----+----+ 
33. rows in set (0.00 sec) 

MySQL中必須是有索引的字段才可以使用AUTO_INCREMENT,刪除重復(fù)數(shù)據(jù)與上例一樣,記得刪除完數(shù)據(jù)把id字段也刪除了,刪除重復(fù)數(shù)據(jù),只保留一條數(shù)據(jù),代碼如下:

1. /* 刪除重復(fù)數(shù)據(jù)，只保留一條數(shù)據(jù) */ 
2. DELETE FROM `noid` 
3. USING `noid`,( 
4.   SELECT DISTINCT MIN(`id`) AS `id`,`name`,`add` 
5.   FROM `noid` 
6.   GROUP BY `name`,`add` 
7.   HAVING COUNT(1) > 1 
8. ) AS `t2` 
9. WHERE `noid`.`name` = `t2`.`name` 
10.   AND `noid`.`add` = `t2`.`add` 
11.   AND `noid`.`id` <> `t2`.`id`; 
12. Query OK, 8 rows affected (0.05 sec) 
13. /* 刪除id字段 */ 
14. ALTER TABLE `noid` DROP `id`; 
15. Query OK, 15 rows affected (0.16 sec) 
16. Records: 15  Duplicates: 0  Warnings: 0 
17. SELECT * FROM `noid`; 
18. +----+------+-----+ 
19. | pk | name | add | 
20. +----+------+-----+ 
21. | a  | abc  | 123 | 
22. | c  | abc  | 321 | 
23. | e  | xzy  | 123 | 
24. | f  | xzy  | 456 | 
25. | i  | xzy  | 789 | 
26. | j  | xzy  | 987 | 
27. | l  | ijk  | 147 | 
28. | n  | ijk  | 852 | 
29. | o  | opq  | 852 | 
30. | p  | opq  | 963 | 
31. | q  | opq  | 741 | 
32. | r  | tpk  | 741 | 
33. | s  | tpk  | 963 | 
34. | u  | wer  | 546 |   //Vevb.com 
35. | w  | once | 546 | 
36. +----+------+-----+ 
37. rows in set (0.00 sec)