获取MySql中重复行的列表 [英] Get list of duplicate rows in MySql

问题描述

我有一个这样的桌子

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

我需要一个查询，该查询将返回具有相同nachname和vorname的记录(在这种情况下，记录1和2)的所有字段(选择*). 谁能帮我这个?谢谢

I need a query that will return all the fields (select *) from the records that have the same nachname and vorname (in this case, records 1 and 2). Can anyone help me with this? Thanks

推荐答案

以下查询将给出重复项列表:

The following query will give the list of duplicates :

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.

BTW The data you posted seems to be wrong "Doe" and "Knight" are a lastname, not a firstname :p.

这篇关于获取MySql中重复行的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！