从结果集中删除可能包含相同电子邮件地址的行

Question

我有以下MySQL查询。

SELECT
    login,
    firstname,
    lastname,
    email
FROM
    xcart_customers
WHERE
    login LIKE 'anonymous%'
AND email NOT IN (
    SELECT
        email
    FROM
        xcart_customers AS cx
    WHERE
        cx.login NOT LIKE 'anonymous%'
)
GROUP BY
    email;

基本上有两组客户，一组是登录的客户，另一组是匿名客户，他们都以“匿名%”的登录开始。

我正在尝试从列表中删除与匿名用户具有相同电子邮件地址的非匿名用户。

我认为上面的查询应该是有效的，但我仍然得到了一些与非匿名用户匹配的电子邮件地址。

login           |  firstname     |   lastname     |   email
---------------------------------------------------------------------------
anonymous-10    |  Eric          |   Byorn        |   byorn@mail.com
---------------------------------------------------------------------------
some_user_name  |  Eric          |   Byorn        |   byorn@mail.com
---------------------------------------------------------------------------

所以我正在尝试解决所有匿名用户，他们只出现在匿名结果中。

Answer 1

最有效、最优雅的解决方案是使用外部连接：

SELECT
  a.login,
  a.firstname,
  a.lastname,
  a.email
FROM xcart_customers a
LEFT JOIN xcart_customers b ON b.email = a.email
  AND b.login NOT LIKE 'anonymous%'
WHERE a.login LIKE 'anonymous%'
AND b.email IS NULL

这里有两个关键的技巧：

1. 由于未命中的左连接返回左连接表中的所有NULL，因此WHERE子句排除具有匹配关键字的行。连接表上的所有条件(即使不是非关键字)必须在连接条件中，即不在WHERE子句中，否则为了有效地将外连接转换为内部连接

请注意，您对GROUP BY的使用是有缺陷的，也是不必要的。