select room from (select * from table where sd = a or sd = b) group by room having count(1) = 2
Select * from TableName A Where Exists (Select 1 from TableName Where sd='B' And room=A.room) And sd='A'
--修改一下 select distinct room from (select * from table where sd = a or sd = b) group by room having count(1) = 2
Select A.room from TableName A Inner Join TableName B On A.room=B.room Where A.sd='A' And B.sd='B'
declare @t table(room varchar(10),sd varchar(10)) insert into @t select 'j101', 'a' union all select 'j101', 'b' union all select 'j102', 'a' union all select 'j102', 'b' union all select 'j103', 'b' union all select 'j104', 'c' union all select 'j104', 'a'select room from @t where sd in('a','b') group by room having count(1)>1
select room from
(select * from table where sd = a or sd = b)
group by room having count(1) = 2
Where Exists (Select 1 from TableName Where sd='B' And room=A.room) And sd='A'
select distinct room from
(select * from table where sd = a or sd = b)
group by room having count(1) = 2
Inner Join TableName B
On A.room=B.room
Where A.sd='A' And B.sd='B'
insert into @t
select 'j101', 'a'
union all select 'j101', 'b'
union all select 'j102', 'a'
union all select 'j102', 'b'
union all select 'j103', 'b'
union all select 'j104', 'c'
union all select 'j104', 'a'select room from @t where sd in('a','b') group by room having count(1)>1