从 SQL Server 中选择具有匹配列的行 [英] Select Rows with matching columns from SQL Server

问题描述

我相当肯定这很简单，但我尝试过的每个示例都失败了.我想查询这样的表

I am fairly certain that this is something simple, but every example I have tried is failing. I want to query a table like this

ID   Part_Type   Station_Type
---  ---------   ------------
1    5           234
2    5           846
3    5           234
4    6           585
5    6           585
6    7           465

并返回第 1 行和第 3 行，以及第 4 行和第 5 行.也就是说，我想返回其中两列匹配的行.它类似于这个问题:SO Question 但只需要在一张桌子上完成.该查询将为每一行找到一个匹配项，但我只需要在两列中有匹配值的行.我如何去找到那个?

and return the rows 1 and 3, as well as 4 and 5. That is, I want to return rows where two of their columns match. It is similar to this question: SO Question but needs to be done on one table only. That query will find a match for every row, but I only want rows that have matching values in two columns. How do I go about find that?

谢谢

推荐答案

您可以使用以下内容:

select t1.id, t1.part_type, t1.station_type
from yourtable t1
where exists (select part_type, station_type
              from yourtable t2
              where t1.part_type = t2.part_type
                and t1.station_type = t2.station_type
              group by part_type, station_type
              having count(id) > 1)

参见SQL Fiddle with Demo

这篇关于从 SQL Server 中选择具有匹配列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！