在表中选择具有特定列的相同数据的两行

There is a column in a table(contracts) called service location. I have to show all the rows where the service locations matches any other row in the table.

表（合同）中有一列称为服务位置。我必须显示服务位置与表中任何其他行匹配的所有行。

    Table Example

 A    B    C
 1    2    3
 3    2    1
 2    5    3

I require a query where the first and second rows will be returned based on a comparison on the second column. I am assuming I will need to use a HAVING COUNT(B) > 1

我需要一个查询，其中第一行和第二行将根据第二列的比较返回。我假设我需要使用HAVING COUNT（B）> 1

I came up with this

我想出了这个

SELECT  `contract_number` 
FROM  `contracts` 
WHERE  `import_id` =  'fe508764-54a9-41f7-b36e-50ebfd95971b'
GROUP BY  `service_location_id` 
HAVING COUNT(`service_location_id` ) >1

But it doesn't generate what I exactly need.

但它不会产生我真正需要的东西。

2 个解决方案

#1

Having would do it, but you would need to use it like this

我会这样做，但你需要像这样使用它

SELECT  *
FROM    Contracts
        INNER JOIN
        (   SELECT  B
            FROM    Contracts
            GROUP BY B
            HAVING COUNT(*) > 1 -- MORE THAN ONE ROW WITH THE SAME VALUE
        ) dupe
            ON dupe.B = Contracts.B

Depending in your indexing you may find a self join performs better though:

根据您的索引，您可能会发现自联接表现更好：

SELECT  DISTINCT t1.*
FROM    contracts t1
        INNER JOIN contract` t2
            ON t1.B = t2.B
            AND t1.A <> t2.A

#2

SELECT * FROM sheet1 WHERE C IN (

SELECT * FROM sheet1 WHERE C IN（

SELECT C FROM sheet1 GROUP BY C HAVING COUNT( C ) >1 ) ORDER BY C LIMIT 0 , 5000

SELECT C FROM sheet1 GROUP BY C HAVING COUNT（C）> 1）ORDER BY C LIMIT 0,5000

#1