Mysql重复行(使用2列检测到重复)

本文介绍了Mysql重复行(使用2列检测到重复)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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:

bugs.mysql/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 )