当前位置：首页 > Mysql教程 > 列表

MySQL取出表中排序数据且不重复 删除重复数据

发布:smiling　来源: PHP粉丝网 　添加日期：2014-10-04 17:58:12　浏览: 评论：0　

在mysql中出现重复的数据时我们有需要进行处理,下面我来给大家介绍关于mysql重复数据的一些处理方法,包括删除重复数据、排序数据且不重复、查询等等.

今日做一个功能需求,从表中排序取出前十个且数据不一致的数据,如下列a表中的值有以下数据:

  1. mysql> select * from a; 
  2. +----+----------+ 
  3. | id | user | 
  4. +----+----------+ 
  5. | 1 | zhangsan | 
  6. | 2 | lisi | 
  7. | 3 | wangwu | 
  8. | 4 | zhangsan | 
  9. | 5 | zhaosi | 
  10. | 6 | wangwu | 
  11. | 7 | lisi | 
  12. | 8 | lisi | 
  13. | 9 | zhaosi | 
  14. +----+----------+ 
  15. 9 rows in set (0.00 sec) 

我们需要取出id最大的前四位,且user不能一致的信息,按照以上信息以及需求,我们要搜索出结果为:

  1. zhaosi 
  2. lisi 
  3. wangwu 
  4. zhangsan 

不能按照普通的做法,代码如下:

  1. mysql> select * from a order by id desc limit 4; 
  2. +----+--------+ 
  3. | id | user | 
  4. +----+--------+ 
  5. | 9 | zhaosi | 
  6. | 8 | lisi | 
  7. | 7 | lisi | 
  8. | 6 | wangwu | 
  9. +----+--------+ 
  10. 4 rows in set (0.00 sec) 

这样搜索出来的有重复值,得使用distinct关键字,代码如下:

  1. mysql> select distinct user from a order by id desc limit 4;  
  2. +----------+ 
  3. | user | 
  4. +----------+ 
  5. | zhaosi | 
  6. | wangwu | 
  7. | lisi | 
  8. | zhangsan | 
  9. +----------+ 
  10. 4 rows in set (0.00 sec) 

其实应该是lisi与wangwu互换一下才是比较理想的,因为lisi最大的ID是8,而wangwu最大的ID是6,可能是lisi有一个ID为2导致的,我们把ID为2的删除,在来试试,代码如下:

  1. mysql> delete from a where id=2; 
  2. Query OK, 1 row affected (0.02 sec) 
  3.  
  4. mysql> select * from a; 
  5. +----+----------+ 
  6. | id | user | 
  7. +----+----------+ 
  8. | 1 | zhangsan | 
  9. | 3 | wangwu | 
  10. | 4 | zhangsan | 
  11. | 5 | zhaosi | 
  12. | 6 | wangwu | 
  13. | 7 | lisi | 
  14. | 8 | lisi | 
  15. | 9 | zhaosi | 
  16. +----+----------+ 
  17. 8 rows in set (0.00 sec) 
  18.  
  19. mysql> select distinct user from a order by id desc limit 4;  
  20. +----------+  --phpfensi.com 
  21. | user | 
  22. +----------+ 
  23. | lisi | 
  24. | zhaosi | 
  25. | wangwu | 
  26. | zhangsan | 
  27. +----------+ 
  28. 4 rows in set (0.00 sec) 

结果正是由于前边有较低的ID记录影响了排序.

虽然这条语句能搜索正确的效果,但可能排序不是那么理想,也就是ID最大的前四位能搜索出来,但在这四位数据里并不是按照ID大小排序的.

例1测试数据,代码如下:

  1. /* 表结构 */ 
  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. /* 插入测试数据 */ 
  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最小的重复数据(只查找id字段),代码如下:

  1. /* 查找id最小的重复数据(只查找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) 

查找所有重复数据,代码如下:

  1. /* 查找所有重复数据 */ 
  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最小的数据外的重复数据,代码如下:

  1. /* 查找除id最小的数据外的重复数据 */ 
  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,表中没有主键(可唯一标识的字段),或者主键并非数字类型(也可以删除重复数据,但效率上肯定比较慢).

例2测试数据,代码如下:

  1. /* 表结构 */ 
  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. /* 测试数据，与上例一样的测试数据，只是主键变为字符串形式 */ 
  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) 

为表添加自增长的id字段,代码如下:

  1. /* 为表添加自增长的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.

删除重复数据与上例一样,记得删除完数据把id字段也删除了,删除重复数据,只保留一条数据,代码如下:

  1. /* 删除重复数据，只保留一条数据 */ 
  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 | 
  35. | w  | once | 546 | 
  36. +----+------+-----+ 
  37.  rows in set (0.00 sec)

Tags: MySQL排序 MySQL删除重复数据

分享到：

相关文章

推荐文章

热门文章

最新评论文章