where not exists (select * from tc where tno=x.tno and cno=c.cno))
sql sever 查询语句
标签：sts code 其他 值传递 bsp style 参考 blog 使用
sql sever 查询语句
相关子查询
select cn
from c
where cno in(select cno from tc where tno=‘t1‘ and cno in(select cno from tc
where tno=‘t3‘))
查询选了c1课程的学生姓名//相关子查询
select sn
from s
where sno in (select sno from sc where cno=‘c1‘ and sc.sno=s.sno)
查询不学数据库的学生学号//相关子查询+连接子查询
select sno
from s
where sno not in (select sno from sc,c where cn=‘数据库‘ and sc.cno=c.cno )
查询赵亦选的课程名
select cn
from c
where cno in (select cno from sc where sno in (select sno from s where sn=‘赵亦‘))//子查询
select cn
from c
where ‘赵亦‘ in (select sn from s,sc where s.sno=sc.sno and c.cno=cno) //连接子查询+相关子查询
select cn
from c
where ‘赵亦‘ in (select sn from s where sno in (select sno from sc where cno=c.cno)) //相关子查询
查询被所以学生选修了课程的课程号，课程名 使用exists
select cno ,cn
from c
where not exists (select * from s where not exists (select * from sc where sno=s.sno and c.cno=cno))
查询被t1，t3和t4老师教了的课程的课程名 使用exists
思路：该课程没有一个老师没教
select cn
from c
where not exists (select * from (select * from t where tno in (‘t1‘,‘t3‘,‘t4‘)) x
where not exists (select * from tc where tno=x.tno and cno=c.cno))
where not exists (select * from tc where tno=x.tno and cno=c.cno))