如何选择每一行的列值不是独特的

小开

如何

SELECT EmailAddress, CustomerName FROM Customers a
WHERE Exists ( SELECT emailAddress FROM customers c WHERE a.customerName != c.customerName AND a.EmailAddress = c.EmailAddress)

小开

只是为了好玩，这里有另一种方法:

;with counts as (
select CustomerName, EmailAddress,
count(*) over (partition by EmailAddress) as num
from Customers
)
select CustomerName, EmailAddress
from counts
where num > 1

小开

您的查询不正确的事情是，您是通过电子邮件和名称进行分组，这形成了一个组，每个独特的电子邮件和名称组合在一起，因此

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)

小开

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

小开

最佳答案

这比EXISTS方法要快得多:

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

小开

而不是在where条件下使用子查询，这会增加记录很大的地方的查询时间。

我建议使用内连接作为解决这个问题的更好选择。

考虑相同的表格，这可以给出结果

SELECT EmailAddress, CustomerName FROM Customers as a
Inner Join Customers as b on a.CustomerName <> b.CustomerName and a.EmailAddress = b.EmailAddress

为了得到更好的结果，我建议你使用CustomerID或你表中的任何唯一字段。可以复制CustomerName。

小开

SELECT        Title, Id
FROM            dbo.TblNews
WHERE        (Title IN
(SELECT  Title
FROM dbo.TblNews AS TblNews_1
GROUP BY Title
HAVING (COUNT(*) > 1)))
ORDER BY Title

标题排序