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

SEO研究中心 SEO研究中心提供免费SEO公开课

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

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

  1. mysql>select*froma;
  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. 9rowsinset(0.00sec)

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

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

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

  1. mysql>select*fromaorderbyiddesclimit4;
  2. +----+--------+
  3. |id|user|
  4. +----+--------+
  5. |9|zhaosi|
  6. |8|lisi|
  7. |7|lisi|
  8. |6|wangwu|
  9. +----+--------+
  10. 4rowsinset(0.00sec)

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

  1. mysql>selectdistinctuserfromaorderbyiddesclimit4;
  2. +----------+
  3. |user|
  4. +----------+
  5. |zhaosi|
  6. |wangwu|
  7. |lisi|
  8. |zhangsan|
  9. +----------+
  10. 4rowsinset(0.00sec)

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

  1. mysql>deletefromawhereid=2;
  2. QueryOK,1rowaffected(0.02sec)
  4. mysql>select*froma;
  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. 8rowsinset(0.00sec)
  19. mysql>selectdistinctuserfromaorderbyiddesclimit4;
  20. +----------+--phpfensi.com
  21. |user|
  22. +----------+
  23. |lisi|
  24. |zhaosi|
  25. |wangwu|
  26. |zhangsan|
  27. +----------+
  28. 4rowsinset(0.00sec)

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

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

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

  1. /*表结构*/
  2. DROPTABLEIFEXISTS`t1`;
  3. CREATETABLEIFNOTEXISTS`t1`(
  4. `id`INT(1)NOTNULLAUTO_INCREMENT,
  5. `name`VARCHAR(20)NOTNULL,
  6. `add`VARCHAR(20)NOTNULL,
  7. PRIMARYKEY(`id`)
  8. )Engine=InnoDB;
  9. /*插入测试数据*/
  10. INSERTINTO`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. rowsinset(0.00sec)

查找id最小的重复数据(只查找id字段),代码如下:

  1. /*查找id最小的重复数据(只查找id字段)*/
  2. SELECTDISTINCTMIN(`id`)AS`id`
  3. FROM`t1`
  4. GROUPBY`name`,`add`
  5. HAVINGCOUNT(1)>1;
  6. +------+
  7. |id|
  8. +------+
  9. |1|
  10. |12|
  11. |19|
  12. |21|
  13. |6|
  14. |9|
  15. +------+
  16. rowsinset(0.00sec)

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

  1. /*查找所有重复数据*/
  2. SELECT`t1`.*
  3. FROM`t1`,(
  4. SELECT`name`,`add`
  5. FROM`t1`
  6. GROUPBY`name`,`add`
  7. HAVINGCOUNT(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. rowsinset(0.00sec)

查找除id最小的数据外的重复数据,代码如下:

  1. /*查找除id最小的数据外的重复数据*/
  2. SELECT`t1`.*
  3. FROM`t1`,(
  4. SELECTDISTINCTMIN(`id`)AS`id`,`name`,`add`
  5. FROM`t1`
  6. GROUPBY`name`,`add`
  7. HAVINGCOUNT(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. rowsinset(0.00sec)

例2,表中没有主键(可唯一标识的字段),或者主键并非数字类型(也可以删除重复数据,但效率上肯定比较慢).

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

  1. /*表结构*/
  2. DROPTABLEIFEXISTS`noid`;
  3. CREATETABLEIFNOTEXISTS`noid`(
  4. `pk`VARCHAR(20)NOTNULLCOMMENT'字符串主键',
  5. `name`VARCHAR(20)NOTNULL,
  6. `add`VARCHAR(20)NOTNULL,
  7. PRIMARYKEY(`pk`)
  8. )Engine=InnoDB;
  9. /*测试数据，与上例一样的测试数据，只是主键变为字符串形式*/
  10. INSERTINTO`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. rowsinset(0.00sec)

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

  1. /*为表添加自增长的id字段*/
  2. ALTERTABLE`noid`ADD`id`INT(1)NOTNULLAUTO_INCREMENT,ADDINDEX`id`(`id`);
  3. QueryOK,23rowsaffected(0.16sec)
  4. Records:23Duplicates:0Warnings: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. rowsinset(0.00sec)

MySQL中必须是有索引的字段才可以使用AUTO_INCREMENT.

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

  1. /*删除重复数据，只保留一条数据*/
  2. DELETEFROM`noid`
  3. USING`noid`,(
  4. SELECTDISTINCTMIN(`id`)AS`id`,`name`,`add`
  5. FROM`noid`
  6. GROUPBY`name`,`add`
  7. HAVINGCOUNT(1)>1
  8. )AS`t2`
  9. WHERE`noid`.`name`=`t2`.`name`
  10. AND`noid`.`add`=`t2`.`add`
  11. AND`noid`.`id`<>`t2`.`id`;
  12. QueryOK,8rowsaffected(0.05sec)
  13. /*删除id字段*/
  14. ALTERTABLE`noid`DROP`id`;
  15. QueryOK,15rowsaffected(0.16sec)
  16. Records:15Duplicates:0Warnings: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. rowsinset(0.00sec)

相关广告
  • MySQL取出表中排序数据且不重复 删除重复数据 MySQL取出表中排序数据且不重复 删除重复数据 MySQL取出表中排序数据且不重复 删除重复数据
相关阅读

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

2019/10/10 17:35:03 | 谷歌SEO算法 | Safari浏览器