SELECT sno FROM select_course WHERE cno IN(1,3) GROUP BY sno
--同时选择了课程1和3的学生select * from select_course A where cno=1 and exists (select * from select_course where sno=A.sno and cno=3)
--改--同时选择了1和3的学生学号select sno from select_course A where cno=1 and exists (select * from select_course where sno=A.sno and cno=3) group by sno order by sno
create table #select_course (sno int,cno int)insert into #select_course select 1,1 insert into #select_course select 1,2 insert into #select_course select 1,3 insert into #select_course select 2,1 insert into #select_course select 2,2 insert into #select_course select 3,3 insert into #select_course select 4,3 insert into #select_course select 4,1--同时选择了1和3的学生学号 select sno from #select_course A where cno=1 and exists (select * from #select_course where sno=A.sno and cno=3)drop table #select_course
那要是求:同时选择了课程1和2和3的学生 sql语句怎么写呢 谢谢啦:)
create table #select_course (sno int,cno int)insert into #select_course select 1,1 insert into #select_course select 1,2 insert into #select_course select 1,3 insert into #select_course select 2,1 insert into #select_course select 2,2 insert into #select_course select 3,3 insert into #select_course select 4,3 insert into #select_course select 4,1--同时选择了课程1、2、3的学生学号 select sno from #select_course A where cno=1 and exists (select * from #select_course where sno=A.sno and cno=2) and exists (select * from #select_course where sno=A.sno and cno=3)drop table #select_course
FROM select_course
WHERE cno IN(1,3)
GROUP BY sno
--同时选择了课程1和3的学生select *
from select_course A
where cno=1 and exists (select * from select_course where sno=A.sno and cno=3)
from select_course A
where cno=1 and exists (select * from select_course where sno=A.sno and cno=3)
group by sno
order by sno
create table #select_course (sno int,cno int)insert into #select_course select 1,1
insert into #select_course select 1,2
insert into #select_course select 1,3
insert into #select_course select 2,1
insert into #select_course select 2,2
insert into #select_course select 3,3
insert into #select_course select 4,3
insert into #select_course select 4,1--同时选择了1和3的学生学号
select sno
from #select_course A
where cno=1 and exists (select * from #select_course where sno=A.sno and cno=3)drop table #select_course
create table #select_course (sno int,cno int)insert into #select_course select 1,1
insert into #select_course select 1,2
insert into #select_course select 1,3
insert into #select_course select 2,1
insert into #select_course select 2,2
insert into #select_course select 3,3
insert into #select_course select 4,3
insert into #select_course select 4,1--同时选择了课程1、2、3的学生学号
select sno
from #select_course A
where cno=1
and exists (select * from #select_course where sno=A.sno and cno=2)
and exists (select * from #select_course where sno=A.sno and cno=3)drop table #select_course