mysql - 如何在使用 where 子句时仅查找一列中的重复值？

Question

目前，我有一个选择查询，在我的客户数据库中查找 X 数量的电子邮件并返回类似这样的内容

SELECT
 first_name,
 last_name,
 email_address,
 date_deleted
FROM CUSTOMERS
WHERE email_address in ('bob.smith@gmail.com', 'john.green@gmail.com', 'simon.anderson@gmail.com', 'carl.thomas@gmail.com');

+----+------------+-----------+---------------------------+--------------+
| id | first_name | last_name | email_address             | date_deleted |
+----+------------+-----------+---------------------------+--------------+
|  1 | bob        | smith     | bob.smith@gmail.com       | 2013-02-18   |
|  2 | NULL       | NULL      | bob.smith@gmail.com       | NULL         |
|  3 | john       | green     | john.green@gmail.com      | NULL         |
|  4 | simon      | anderson  | simon.anderson@gmail.com  | NULL         |
|  5 | carl       | thomas    | carl.thomas@gmail.com     | 2012-09-20   |
|  6 | NULL       | NULL      | carl.thomas@gmail.com     | NULL         |
+----+------------+-----------+---------------------------+--------------+

电子邮件地址列中存在重复的电子邮件地址。我想修改我的查询，以便它仅返回该列中的重复项，并通过 date_deleted 进行过滤。这可能吗？

+----+------------+-----------+---------------------------+--------------+
| id | first_name | last_name | email_address             | date_deleted |
+----+------------+-----------+---------------------------+--------------+
|  1 | bob        | smith     | bob.smith@gmail.com       | 2013-02-18   |
|  2 | carl       | thomas    | carl.thomas@gmail.com     | 2012-09-20   |
+----+------------+-----------+---------------------------+--------------+

Answer 1

在列中查找重复值的最佳方法是使用 HAVING 语句，正如 Mike Tung 在他的评论中提到的那样。

SELECT email_address
  FROM CUSTOMERS
 GROUP BY email_address
HAVING COUNT(1) > 1

这将为您返回以下结果:

+---------------------------+
| email_address             |
+---------------------------+
| bob.smith@gmail.com       |
| carl.thomas@gmail.com     |
+---------------------------+

然后，您可以使用此查询的结果执行您需要的任何其他逻辑。如果我正确理解您的问题，您希望查找具有重复电子邮件地址且 date_deleted 非空的记录。如果是这样，您可以通过以下查询获取该信息:

SELECT CUS.first_name
       ,CUS.last_name
       ,CUS.email_address
       ,CUS.date_deleted
  FROM CUSTOMERS AS CUS
  JOIN (SELECT email_address
          FROM CUSTOMERS
         GROUP BY email_address
        HAVING COUNT(1) > 1) AS C
       ON C.email_address = CUS.email_address
 WHERE CUS.date_deleted IS NOT NULL

这应该给你结果:

+----+------------+-----------+---------------------------+--------------+
| id | first_name | last_name | email_address             | date_deleted |
+----+------------+-----------+---------------------------+--------------+
|  1 | bob        | smith     | bob.smith@gmail.com       | 2013-02-18   |
|  2 | carl       | thomas    | carl.thomas@gmail.com     | 2012-09-20   |
+----+------------+-----------+---------------------------+--------------+