SQL 如何选择列值不不同的每一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13146304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Select Every Row Where Column Value is NOT Distinct
提问by Grasshopper
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 [email protected]
Christy [email protected]
Jason [email protected]
Eric [email protected]
John [email protected]
I need the query to return:
我需要查询返回：
Aaron [email protected]
Christy [email protected]
John [email protected]
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
回答by Serj Sagan
This is significantly faster than the EXISTSway:
这比以下EXISTS方式快得多：
SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
(SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)
回答by Seasoned
The thing that is incorrect with your query is that you are grouping by email and name, that forms a group of each unique set of email and name combined together and hence
您的查询不正确的是您按电子邮件和姓名分组，这形成了一组由每组唯一的电子邮件和姓名组合在一起的组，因此
aaron and [email protected]
christy and [email protected]
john and [email protected]
are treated as 3 different groups rather all belonging to 1 single group.
被视为 3 个不同的组，而不是都属于 1 个单独的组。
Please use the query as given below :
请使用下面给出的查询：
select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)
回答by Marc
How about
怎么样
SELECT EmailAddress, CustomerName FROM Customers a
WHERE Exists ( SELECT emailAddress FROM customers c WHERE a.customerName != c.customerName AND a.EmailAddress = c.EmailAddress)
回答by Nisar
select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1
回答by Chad
Just for fun, here's another way:
只是为了好玩，这是另一种方式：
;with counts as (
select CustomerName, EmailAddress,
count(*) over (partition by EmailAddress) as num
from Customers
)
select CustomerName, EmailAddress
from counts
where num > 1
回答by Naveen Kishan
Rather than using sub queries in where condition which will increase the query time where records are huge.
而不是在 where 条件下使用子查询，这会增加记录巨大的查询时间。
I would suggest to use Inner Join as a better option to this problem.
我建议使用 Inner Join 作为解决此问题的更好选择。
Considering the same table this could give the result
考虑同一张表，这可能会给出结果
SELECT EmailAddress, CustomerName FROM Customers as a
Inner Join Customers as b on a.CustomerName <> b.CustomerName and a.EmailAddress = b.EmailAddress
For still better results I would suggest you to use CustomerIDor any unique field of your table. Duplication of CustomerNameis possible.
为了获得更好的结果，我建议您使用CustomerID或 表格中的任何唯一字段。复制CustomerName是可能的。
回答by Rashmi Ranjan Ransingh
Well there is a slight change to find the non distinct rows..
好吧，找到不不同的行有一个细微的变化。
SELECT EmailAddress, CustomerName FROM Customers WHERE EmailAddress NOT IN
(SELECT EmailAddress FROM Customers GROUP BY EmailAddress HAVING COUNT(*) > 1)
