mysql - 查找名字/姓氏交换的重复用户

发布于 2024-12-09 12:08:04 字数 207 浏览 2 评论 0原文

我需要查找已在表中插入两次的用户，但其名字和名称都相同。姓氏互换了。

例如，Bob Smith 在数据库中为“

firstname:Bob 
lastname:Smith
&
firstname:Smith
lastname:Bob

查找这些用户的最佳查询是什么？”。

服务器运行mysql。

原文

I need to find users that have been inserted twice in a table, but with their first name & last name swapped.

e.g. Bob Smith is in the database as

firstname:Bob 
lastname:Smith
&
firstname:Smith
lastname:Bob

What's the best query to find those users?

The server runs mysql.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱你是孤单的心事 2024-12-16 12:08:04

Select 
  *
FROM UserTable ut
JOIN UserTable ut2 on ut2.firstname = ut.lastname and ut2.lastname = ut.firstname

Select 
  *
FROM UserTable ut
JOIN UserTable ut2 on ut2.firstname = ut.lastname and ut2.lastname = ut.firstname

回复收藏 0 原文

花想c 2024-12-16 12:08:04

SELECT
    firstname, lastname
FROM
    (
    SELECT firstname, lastname FROM MyTable -- WHERE firstname <> lastname
    UNION ALL
    SELECT lastname, firstname FROM MyTable -- WHERE firstname <> lastname
    ) foo
GROUP BY
   firstname, lastname
HAVING 
   COUNT(*) > 1

SELECT
    firstname, lastname
FROM
    (
    SELECT firstname, lastname FROM MyTable -- WHERE firstname <> lastname
    UNION ALL
    SELECT lastname, firstname FROM MyTable -- WHERE firstname <> lastname
    ) foo
GROUP BY
   firstname, lastname
HAVING 
   COUNT(*) > 1

回复收藏 0 原文

俏︾媚 2024-12-16 12:08:04

SELECT orig.firstname, orig.lastname
FROM yourtable AS orig
INNER JOIN yourtable AS dupes ON orig.firstname = dupe.lastname AND orig.lastname = dupe.firstname

基本上，对用户表进行自联接，但仅对发生 fn/ln dupe-swap 的记录进行。

SELECT orig.firstname, orig.lastname
FROM yourtable AS orig
INNER JOIN yourtable AS dupes ON orig.firstname = dupe.lastname AND orig.lastname = dupe.firstname

Basically, do a self-join on the user table, but only on the records where the fn/ln dupe-swap occurs.

回复收藏 0 原文

~没有更多了~