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

我需要运行一个选择语句，返回列值不明显的所有行(例如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

当前回答

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

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

2012-10-30 19:38:15

其他回答

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

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

2012-10-30 19:38: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)

2012-10-30 19:49:14

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

标题排序

2021-04-11 13:33:07

如何

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

2012-10-30 19:34:08

这比EXISTS方法要快得多:

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

2013-10-11 20:26:51

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

推荐文章

最新文章

标签