从列中检索共享重复值的所有记录

I would like to return all records from a table that share duplicate values from a given specific column, in this case the BIN column.

我想从表中返回来自给定特定列的重复值的所有记录，在本例中为BIN列。

Create table #TempCompany (CompanyID varchar(6), Name varchar(50),BIN varchar(6))
Insert into #TempCompany (CompanyID,Name,BIN) 

SELECT '000001','ABC Company','000000' Union All
SELECT '000002','DEF Company','000001' Union All
SELECT '000003','GHI Company','000001' Union All
SELECT '000004','JKL Company','000002' Union All
SELECT '000005','LMN Company','000003' Union All
SELECT '000006','OPQ Company','000003' Union All
SELECT '000007','RST Company','000003'

Drop Table #TempCompany

I would like to return this result:

我想返回这个结果：

CompanyID   Name    BIN
000002  DEF Company 000001
000003  GHI Company 000001
000005  LMN Company 000003
000006  OPQ Company 000003
000007  RST Company 000003

2 个解决方案

#1

You can do this using window functions:

您可以使用窗口函数执行此操作：

select tc.*
from (select tc.*,
             count(*) over (partition by bin) as cnt
      from #TempCompany tc
     ) tc
where cnt >= 2;

#2

One way of doing this is with a subquery that uses a having clause to locate such records:

执行此操作的一种方法是使用子查询使用having子句来查找此类记录：

SELECT *
FROM   #TempCompany
WHERE  BIN IN (SELECT   BIN
               FROM     #TempCompany
               GROUP BY BIN
               HAVING   COUNT(*) > 1)

#1