mysql - 查找行，其中名称变体，但不是电子邮件地址

Question

我有一个问题如下，我的脑子里烧得很厉害。

想象下表(second_id 不重要，说我应该使用 unique() 在我的情况下不起作用)

create table adressen (

id int PRIMARY KEY AUTO_INCREMENT,
second_id int,
name char(64),
email char(64)
);

INSERT INTO adressen (second_id, name, email) VALUES ('1','max','max@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('1','peter','peter@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('1','emma','emma@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('2','max','max@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('2','peeter','peter@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('2','emma','emma@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('3','maax','max@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('3','elisa','elisa@test.com');
INSERT INTO adressen (second_id, name, email) VALUES ('3','eemmaa','emma@test.com');

如您所见，有多个用户使用相同的邮件，但名称不同。我正在尝试选择存在多个名称的所有邮件。

在这种情况下，它将是:

• emma@test.com 两个不同的名字(emma, eemmaa)

• max@test.com 两个不同的名字(max, maax)

• peter@test.com 两个不同的名字(peter, peeter)

因此，对于名称和邮件的每个“集合”，输出应为 1 行，例如:

emma@test.com , emma
emma@test.com , eemmaa
max@test.com, max
max@test.com, maxx
petertest.com, peter
petertest.com, peeter

我不想获取邮件和姓名相同的行。如果我的表中只有一个条目(在本例中为 karl)，我也不想得到一行

非常感谢您的帮助!

Answer 1

使用子查询查找具有多个不同name 的所有mail 值，并与表本身连接。

SELECT DISTINCT a.mail, a.name
    FROM tbl a
    JOIN (
              SELECT mail
                FROM tbl
               GROUP BY mail
              HAVING COUNT(DISTINCT name) > 1
         ) b ON a.mail = b.mail
   ORDER BY a.mail, a.name;

DEMO

这基于 O. Jones 的回答，但添加了 COUNT(DISTINCT name) 以便具有相同名称和电子邮件的行不会被计为重复项。