如何选择列值不不同的每一行 [英] How to Select Every Row Where Column Value is NOT Distinct

问题描述

我需要运行一个 select 语句，该语句返回列值不明确的所有行(例如 EmailAddress).

I need to run a select statement that returns all rows where the value of a column is not distinct (e.g. EmailAddress).

例如，如果表格如下所示:

For example, if the table looks like below:

CustomerName     EmailAddress
Aaron            [e-mail]
Christy          [e-mail]
Jason            [e-mail]
Eric             [e-mail]
John             [e-mail]

我需要返回查询:

Aaron            [e-mail]
Christy          [e-mail]
John             [e-mail]

我已经阅读了很多帖子并尝试了不同的查询都无济于事.我认为应该工作的查询如下.有人可以提出替代方案或告诉我我的查询可能有什么问题吗?

I have read many posts and tried different queries to no avail. The query that I believe should work is below. Can someone suggest an alternative or tell me what may be wrong with my query?

select EmailAddress, CustomerName from Customers
group by EmailAddress, CustomerName
having COUNT(distinct(EmailAddress)) > 1

推荐答案

这明显比 EXISTS 方式快:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

这篇关于如何选择列值不不同的每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！