34,838
社区成员




select distinct class from 表 as a
where exists(select * from 表 where class=a.class and type='A')
and exists(select * from 表 where class=a.class and type='B')
declare @tb table(id int,class int,type varchar(50))
insert into @tb select 1,1,'A'
insert into @tb select 2,1,'B'
insert into @tb select 3,2,'A'
insert into @tb select 4,1,'D'
insert into @tb select 5,2,'C'
insert into @tb select 6,1,'C'
select class from @tb where type in('A','B','C','D') group by class having count(distinct type)=4
/*
class
-----------
1
(1 行受影响)
*/
declare @tb table(id int,class int,type varchar(50))
insert into @tb select 1,1,'A'
insert into @tb select 2,1,'B'
insert into @tb select 3,2,'A'
insert into @tb select 4,1,'D'
insert into @tb select 5,2,'C'
select distinct class from @tb a where exists (select 1 from @tb where class=a.class and type='A') and type='B'
/*
class
-----------
1
(1 行受影响)
*/
select class,* from tbName where type in('A','B')
create table #t1(id int,class int, [type] nvarchar(10))
insert into #t1
select 1, 1 ,'A' union all
select 2, 1 , 'B' union all
select 3 , 2 , 'A' union all
select 4 ,1 , 'D' union all
select 5 ,2, 'C'
select * from #t1
select a.class from #t1 as a
join #t1 as b on a.class = b.class
where a.[type] = 'A' and b.[type] = 'B'
declare @tb table(id int,class int,type varchar(50))
insert into @tb select 1,1,'a'
insert into @tb select 2,1,'b'
insert into @tb select 3,2,'a'
insert into @tb select 4,1,'d'
insert into @tb select 5,2,'c'
select a.class
from @tb a inner join @tb b
on a.class = b.class and b.type='b' and a.type = 'A'
select class
from 表
group by class
having count(distinct type) >= 2
declare @tb table(id int,class int,type varchar(50))
insert into @tb select 1,1,'a'
insert into @tb select 2,1,'b'
insert into @tb select 3,2,'a'
insert into @tb select 4,1,'d'
insert into @tb select 5,2,'c'
select a.class
from @tb a
inner join (select * from @tb where type = 'B') b on a.class = b.class
where a.type = 'A'
/*
class
-----------
1
(所影响的行数为 1 行)
*/
declare @tb table(id int,class int,type varchar(50))
insert into @tb select 1,1,'a'
insert into @tb select 2,1,'b'
insert into @tb select 3,2,'a'
insert into @tb select 4,1,'d'
insert into @tb select 5,2,'c'
select * from @tb t where exists(
select 1 from @tb where class=t.class and type in('a','b')
group by class
having count(distinct type)=2
)