我需要运行一条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
发布于 2013-10-12 04:26:51
这比EXISTS
方式快得多:
SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
(SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)
发布于 2012-10-31 03:49:15
查询错误之处在于,您是按电子邮件和姓名进行分组的,这形成了组合在一起的每一组唯一的电子邮件和姓名,因此
aaron and aaron@gmail.com
christy and aaron@gmail.com
john and aaron@gmail.com
被视为3个不同的组,而不是都属于一个单独的组。
请使用如下所示的查询:
select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)
发布于 2013-07-03 14:29:21
select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1
https://stackoverflow.com/questions/13146304
复制相似问题