如何选择列值不明确的每一行

Question

我需要运行一个select语句，返回列值不明显的所有行（例如EmailAddress）。

例如，如果该表如下所示：

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]

我看了很多帖子，尝试了不同的查询，但都无济于事。我认为应该工作的查询如下。有人能建议一个替代方案或者告诉我我的查询可能有什么问题吗？

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

Answer 1

select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1

Answer 2

您的查询不正确的地方在于，您是按电子邮件和名称进行分组的，这形成了一组由每个唯一的电子邮件和名称组合而成的组，因此

aaron and [e-mail]
christy and [e-mail]
john and [e-mail]

被视为3个不同的组，而都属于一个单一的组。

请使用下面给出的查询：

select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)

Answer 3

这比现有的方式快得多：

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