MySQL:在我的用户表中查找重复名称 [英] MySQL: Finding repeated names in my User table

问题描述

我想查找名称在我的用户表中至少出现两次的所有用户.'email' 是唯一的字段，但 'firstName' 和 'lastName' 的组合不一定是唯一的.

I want to find all users whose name appears at least twice in my User table. 'email' is a unique field, but the combination of 'firstName' and 'lastName' is not necessarily unique.

到目前为止，我提出了以下查询，速度非常慢，我什至不确定它是否正确.请让我知道重写此内容的更好方法.

So far I have come up with the following query, which is very slow, and I am not even sure it is correct. Please let me know a better way to rewrite this.

SELECT CONCAT(u2.firstName, u2.lastName) AS fullName
FROM cpnc_User u2
WHERE CONCAT(u2.firstName, u2.lastName) IN (

SELECT CONCAT(u2.firstName, u2.lastName) AS fullNm
FROM cpnc_User u1
GROUP BY fullNm
HAVING COUNT(*) > 1

)

另外，请注意，上面返回了至少出现两次的名称列表(无论如何，我认为是这样)，但我真正想要的是这些名称的所有用户id"字段的完整列表.因此，每个名称至少出现两次，因此将与至少两个主键id"字段相关联.

Also, note that the above returns the list of names that appear at least twice (I think so, anyway), but what I really want is the complete list of all user 'id' fields for these names. So each name, since it appears at least twice, will be associated with at least two primary key 'id' fields.

感谢您的帮助！约拿

推荐答案

SELECT u.*
FROM cpnc_User u JOIN
(
    SELECT firstName, lastName
    FROM cpnc_User
    GROUP BY firstName, lastName
    HAVING COUNT(*) > 1
) X on X.firstName = u.firstName AND x.lastName = u.lastName
ORDER BY u.firstName, u.lastName

不需要拼凑一个字段，单独使用2个字段即可

There is no need to make up a concatenated field, just use the 2 fields separately

这篇关于MySQL:在我的用户表中查找重复名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！