mysql - 根据两个字段查找重复记录

Question

我正在尝试修改此问题中已接受的答案:Find duplicate records in MySQL为了根据名字和姓氏两个字段查找表中的重复记录。

这是原始查询，可以正常工作并根据姓氏查找重复记录...

SELECT Firstname, candidate.Lastname FROM candidate
INNER JOIN (SELECT Lastname FROM candidate
GROUP BY Lastname HAVING count(UserID) > 1) dup ON 
candidate.Lastname = dup.Lastname ORDER BY Lastname

现在我也想在其中添加名字...但我不太确定我在做什么，以下不起作用:

SELECT candidate.Firstname, candidate.Lastname FROM candidate
INNER JOIN (SELECT Firstname AND Lastname FROM candidate
GROUP BY Lastname HAVING count(UserID) > 1) dup ON 
candidate.Lastname = dup.Lastname AND candidate.Firstname = dup.Firstname 
ORDER BY Lastname

Answer 1

看起来您刚刚在子查询中的语法有点错误，也许这就是您想要的？

SELECT candidate.Firstname, candidate.Lastname 
FROM candidate
INNER JOIN (
    SELECT Firstname, Lastname 
    FROM candidate
    GROUP BY Firstname, Lastname 
    HAVING COUNT(UserID) > 1
    ) dup 
    ON candidate.Lastname = dup.Lastname AND candidate.Firstname = dup.Firstname
ORDER BY Lastname