select a.class from ta a left join (select * from ta where type = 'B') b on a.class = b.class where a.type = 'A'
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 )id class type 1 1 a 2 1 b 4 1 d
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 行) */
select class from 表 group by class having count(distinct type) >= 2
-- -- select * from student -- -- create table data( -- -- idc char(2), -- -- class char(2), -- -- type char(1)) -- -- select * from data -- -- insert into data -- -- values ('5','2','c'); -- -- select * from data -- select * from data -- where type ='a'or 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'
我的方法简单 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 class ,type from @tb where type='a' or type='b'如果多就用type in ('a','b','c','d')
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 #t1select a.class from #t1 as a join #t1 as b on a.class = b.class where a.[type] = 'A' and b.[type] = 'B'
select class from 表 where type in('a','b') group by class having count(distinct type) >= 2
select class from 表 where type='A' intersect select class from 表 where type='B'或者 select class from 表 where type='A' and class in ( select class from 表 where type='B' );
select ....from...where ...or...
select class,* from tbName where type in('A','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 distinct class from @tb a where exists (select 1 from @tb where class=a.class and type='A') and type='B'/* 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' 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 行受影响) */
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')
from ta a
left join (select * from ta where type = 'B') b on a.class = b.class
where a.type = 'A'
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
)id class type
1 1 a
2 1 b
4 1 d
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 行)
*/
select class
from 表
group by class
having count(distinct type) >= 2
-- -- create table data(
-- -- idc char(2),
-- -- class char(2),
-- -- type char(1))
-- -- select * from data
-- -- insert into data
-- -- values ('5','2','c');
-- -- select * from data
-- select * from data
-- where type ='a'or 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'
我是想看一下有什么好的办法可以解决这一类问题上面有不少兄弟的答复都可以解决但如果再增加几个条件呢,比如要有'A','B','C','D','E'的class选出来
这样答复中的解法就有点繁琐了
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 class ,type from @tb
where type='a' or type='b'如果多就用type in ('a','b','c','d')
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 #t1select a.class from #t1 as a
join #t1 as b on a.class = b.class
where a.[type] = 'A' and b.[type] = 'B'
from 表
where type in('a','b')
group by class
having count(distinct type) >= 2
from 表
where type='A'
intersect
select class
from 表
where type='B'或者
select class
from 表
where type='A' and class in
(
select class
from 表
where type='B'
);
select class,* from tbName where type in('A','B')
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 行受影响)
*/
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 行受影响)
*/
where exists(select * from 表 where class=a.class and type='A')
and exists(select * from 表 where class=a.class and type='B')