MS SQL查询在一个表中查找公共行 [英] MS SQL query to find the common rows in one tables

问题描述

I am in need of a query to find the common rows in one tables.

Input
 -----------------------------------------------------------------
ID	Name	         ObjectName 
1	StationTypeB          S011
1	StationTypeA          S013
1	StationTypeA          S014 
1       StationTypeA          S17
2	AreaA                 S013
2	AreaA	              S014
2	AreaA	              B015
2	AreaB	              S013

How to find the  common  ObjectName of StationTypeA  and  AreaA
Output
 -----------------------------------------------------------------
ID	Name	         ObjectName  
2	AreaA               S013
2	AreaA	           S014

我有什么试过：

What I have tried:

How to find the  common  ObjectName of StationTypeA  and  AreaA
Output

推荐答案

查找al l超过1行的记录

Finds all records with more than 1 row

SELECT Id, Name, ObjectName, COUNT(*) FROM YourTableName
GROUP BY Id, Name, ObjectName
HAVING COUNT(*) > 1

显示您发布的结果

Displays results like you've posted

SELECT Id, Name, ObjectName FROM YourTableName
GROUP BY Id, Name, ObjectName
HAVING COUNT(*) > 1

HAVING COUNT（*）> ; 1 显示基于组的出现次数超过1次的行。

HAVING COUNT(*) > 1 shows rows that appear more than 1 time based on the group by.

SELECT Id, Name, ObjectName FROM YourTableName where ObjectName in(SELECT ObjectName Where Name='StationTypeA') and Name='AreaA'

此查询将给出以下输出

this query will give below output

ID	Name	         ObjectName  
2	AreaA              S013
2	AreaA	           S014

这篇关于MS SQL查询在一个表中查找公共行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！