有一个学生表S(SNO,SNAME),课程表(CNO,CNAME),选课表SC(SNO,CNO,GRADE),现在要选出选修了全部课程的学生的姓名,SQL语句该怎么写。求一个执行效率比较高的SQL语句。
解决方案 »
- 求助高手:找到的程序集清单定义与程序集引用不匹配
- asp.net Timer控件刷新很慢
- asp.net中, 把一段文本(c++源文件),在服务器端处理后(改变某些行的字体,颜色),显示到客户端.
- 怎么使textbox不能输入第二个"-"?
- 請問哪有MSDN2003(繁體)下載?注意,是繁體啊
- DataGrid 如何隐藏第一行数据.
- 简体下开发的程序编译后,安装在繁体服务器上出现乱码,如何解决?
- 大家帮我写一下这个方法。它提示没有fill方法,怎么办呢?
- 怎样在页面过度期间显示Loading提示?
- 解析Json
- 新手有个传值的小问题请高手解答一下
- 用VS2005开发生成HTML的项目,郁闷到极点.
这种东西完全可以用StoredProcedure来简化和提高效率
from s
where not existes (select * from 课程表,选课表SC where c.cno=sc.cno and s.sno=sc.sno)
代码经过测试
select * from studenttb where sno in
(select sno from selectclasstb group by sno having count(cno)= (select count(cno) from classtb) )
(select sno from 选课表 group by sno having count(cno)= (select count(cno) from 课程表) )
where sno
in(select sno from sc where cno in(1,2))
经测试可以
加题了,求课程名为A的成绩比课程名为B的成绩高的学生的学号及姓名
-----------------------------------
select * from S
inner join
(
select SNO from SC SA
inner join C CA on SA.CNO = CA.CNO and CA.CName = ‘A’
inner join SC SB on SA.SNO = SC.SBO
inner join C CB on SB.CNO = CB.CNO and CB.CName = 'B'
where SA.Grade > SB.Grade
) T
on S.SNO = T.SNO效率可能不高
inner join
(
select SNO,SA.Grade AGrade, SB.Grade BGrade from SC SA
inner join C CA on SA.CNO = CA.CNO and CA.CName = ‘A’
inner join SC SB on SA.SNO = SC.SBO
inner join C CB on SB.CNO = CB.CNO and CB.CName = 'B'
where SA.Grade > SB.Grade
) T
on S.SNO = T.SNO
(select sno from 选课表 where cno ='课程1' or cno ='课程2' group by sno having count(sno)=2) and cno='课程1'and grade > (select grade from 选课表 where sno = a.sno and cno='课程2'))
where 选课表.sno in (select sno from 选课表 a where sno in
(select sno from 选课表 where cno ='课程1' or cno ='课程2' group by sno having count(sno)=2)
and cno='1'and grade > (select grade from 选课表 where sno = a.sno and cno='2'))
and 学生表.sno = 选课表.sno
and 课程表.cno = 选课表.cno
and (选课表.cno = '课程1' or 选课表.cno = '课程2')
select distinct a.sno,c.grade as grade1,d.grade as grade2 from selectclasstb a
inner join selectclasstb c on c.sno = a.sno and c.cno = '1'
inner join selectclasstb d on d.sno =a.sno and d.cno='2'
where c.grade>=d.grade
select * from c AS y where y.cno not in
(select cno from sc where sno = x.sno)