获取MySql中重复行的列表

Question

我有一个像这样的表格：

ID     nachname     vorname
1       john         doe
2       john         doe
3       jim          doe
4       Michael     Knight

我需要一个查询，它将从具有相同姓和名字（在这种情况下是记录1和2）的记录中返回所有字段（选择*）。 有人可以帮我吗？谢谢

- user347033

Answer 1

下面的查询将列出重复项：

SELECT n1.* FROM table n1
inner join table n2 on n2.vorname=n1.vorname and n2.nachname=n1.nachname
where n1.id <> n2.id

顺便说一下，您发布的数据似乎是错误的，“Doe”和“Knight”是姓氏而不是名字 :p。

- wimvds

---

我只需要添加select distinct（查询返回了两次相同的行）。感谢你的帮助。 - user347033

注意：如果“vorname”和“nachname”字段可为空，则应使用“coalesce”函数进行比较。请参见：https://dev59.com/LWkw5IYBdhLWcg3w2-Ik#62260762 - domis86

@domis86：或者你可以使用 NULL 安全的等于运算符 (<=>)，它可以比较可空列。参考文献：https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to - Lucio Mollinedo

Answer 2

您的问题的一般解决方案是形如以下查询：

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

此代码将为表中的每组重复行返回一行结果。结果中的最后一列是特定值的重复次数。

---

如果您真的想要ID，请尝试这样做：

SELECT id FROM 
t1, 
( SELECT col1, col2, count(*)
  FROM t1
  GROUP BY col1, col2
  HAVING count(*) > 1 ) as t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2 

不过我还没有测试过

- ewernli

---

这实际上不会返回所有行，它只会找到重复的行。 - jle

这太贵了，你可以使用简单的连接来解决它（看看我的答案:p）。 - wimvds

Answer 3

您可以使用自连接来完成此操作：

select distinct t1.id from t as t1 inner join t as t2 
on t1.col1=t2.col1 and t1.col2=t2.col2 and t1.id<>t2.id

t1.id<>t2.id是必要的，以避免id与自身匹配。(如果您只想从每组重复项中获取1行，可以使用t1.id<t2.id).

- David Gelhar

---

不行，那个只会返回一个包含两个匹配记录的行，而不是应该返回的两行... - wimvds

@wimvds 如果你想要所有重复的行（而不是每个重复集合中的1行），则应使用<>。 - David Gelhar

Answer 4

select * from table AS t1 inner join
(select max(id) As id,nachname,vorname, count(*) 
from t1 group by nachname,vorname 
having count(*) >1) AS t2 on t1.id=t2.id

这应该返回表中所有重复的nachname和vorname列。我建议将*更改为您需要的确切列。

编辑：我添加了一个max（id），以便group by不会成为问题。我的查询不如我想要的那么优雅。可能有更简单的方法来做到这一点。

- jle

---

嗯...我现在明白你的意思了。但我相当确定你的查询是错误的。如果你没有用它来进行“group by”，那么你不能返回id。 - ewernli

那个连接不起作用 - 在t2查询中没有id列。 - David Gelhar

这就是明显的错误...如果你使用MySQL，group by实际上会消除任何重复项，因为你只对nachname和vorname进行分组，所以它将返回1行，1个ID，而不是所有独特的行，正如你可能期望的那样（试一下，你就会看到）。哦，还有任何其他RDBMS都会抱怨你的group by（这是我认为唯一正确的方法，我讨厌MySQL试图猜测你想要什么并执行这些错误的查询，而不是抛出一个错误）。 - wimvds