Mysql重复行(使用2列检测到重复) [英] Mysql Duplicate Rows ( Duplicate detected using 2 columns )

问题描述

如何删除此设置中的重复项?

How to remove duplicated in this setup?

id    A       B 
----------------
1     apple   2  
2     orange  1       
3     apple   2   
4     apple   1 

在这里，我想删除两次的(apple，2). ID号是唯一的.如果没有，我将使用DISTINCT关键字.我可以通过A列和B列创建一个键，然后使用DISTINCT关键字获得所需的内容吗?非常感谢您的答复.

In here I want to remove (apple,2) which occurs twice. The id numbers are unique. I would use DISTINCT keyword if it were not. Can I some how make a key out of columns A and B and then use the DISTINCT keyword on that to get what I need ? Many thanks for your replies.

推荐答案

delete from myTable 
where id not in
(select min(id)
from myTable
group by A, B)

即括号中的select返回A和B的每个分组的第一个ID；删除不在此集合中的所有ID，将删除A-B组合首次出现后的所有出现.

i.e. the select in brackets returns the first id for each grouping of A and B; deleting all ids that are not in this set will remove all occurences of an A-plus-B combination that are "subsequent" to its first occurrence.

编辑:此语法似乎有问题:请参见错误报告:

EDIT: this syntax seems to be problematic: see bug report:

http://bugs.mysql.com/bug.php?id=5037

可能的解决方法是:

delete from myTable 
where id not in
(
      select minid from 
      (select min(id) as minid from myTable group by A, B) as newtable
) 

这篇关于Mysql重复行(使用2列检测到重复)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！