我需要运行一个 select 语句,它返回列的值不不同的所有行(例如 EmailAddress)。
例如,如果表格如下所示:
CustomerName EmailAddress Aaron aaron@gmail.com Christy aaron@gmail.com Jason jason@gmail.com Eric eric@gmail.com John aaron@gmail.com
我需要查询返回:
Aaron aaron@gmail.com Christy aaron@gmail.com John aaron@gmail.com
我已经阅读了很多帖子并尝试了不同的查询,但无济于事。我认为应该工作的查询如下。有人可以提出替代方案或告诉我我的查询可能有什么问题吗?
select EmailAddress, CustomerName from Customers group by EmailAddress, CustomerName having COUNT(distinct(EmailAddress)) > 1
这比以下EXISTS方式快得多:
EXISTS
SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)