SQL写出来是这样的
1、 select s_id,s_name from student where s_id not in ( select distinct student.s_id from student, score, course, teacher where student.s_id=score.s_id and score.c_id=course.c_id and course.t_id=teacher.t_id and teacher.t_name='王某')
2、 select student.s_id, student.s_name from student,score where student.s_id=score.s_id and c_id in (select score.c_id from student,score where student.s_id=score.s_id and student.s_name='李某')SQL处理这种多表关联再加嵌套的查询一向比较麻烦,写得长还在其次,关键是看着比较晕,时间稍长一点,想再看懂去修改和重写出来的成本是差不多的。SQL本来就不适合用来完成这种复杂的查询,不过用来当考题还是不错的。集算器搞起来容易点,虽然长度差不多,但分步书写和引用机制可以去除关联运算和子查询,编写难度和易读性都改善很多。
1、 select s_id,s_name from student where s_id not in ( select distinct student.s_id from student, score, course, teacher where student.s_id=score.s_id and score.c_id=course.c_id and course.t_id=teacher.t_id and teacher.t_name='王某')
2、 select student.s_id, student.s_name from student,score where student.s_id=score.s_id and c_id in (select score.c_id from student,score where student.s_id=score.s_id and student.s_name='李某')SQL处理这种多表关联再加嵌套的查询一向比较麻烦,写得长还在其次,关键是看着比较晕,时间稍长一点,想再看懂去修改和重写出来的成本是差不多的。SQL本来就不适合用来完成这种复杂的查询,不过用来当考题还是不错的。集算器搞起来容易点,虽然长度差不多,但分步书写和引用机制可以去除关联运算和子查询,编写难度和易读性都改善很多。
解决方案 »
- 怎么样解决PLS-00302: 必须声明 组件这个错误请大侠不吝赐教
- Windows7 下Oracle Developer Suite安装关于虚拟内存的问题
- 关于一个sql语句的问题
- oracle如何用SQL语句将结果集导出成XML
- 对于一张表的大小,oracle有没有限制呢????
- 帮我看看,崩溃了!如此简单就弄不对!
- 请教,数据库表建立好以后,加一个默认值怎么加呢?
- oracle9.0在xp下安装,要先装什么软件吗?
- 诸位高手:一个sql语句的问题
- 求帮助!SQL语句查询速度太慢的问题。。
- 返回今天是星期幾--(若當天是星期五,則1.1返回星期五--1.2返回 Friday) 大哥们必须得用orcale
- 編寫一個函數,实现加減乘除的功能,要求有異常處理功能,兄弟必须得用orcale写哦
--1
select st.s_id, st.s_name
from student st
where not exists (select 1
from score s
left join course co
on s.c_id = co.c_id
left join teacher t
on co.t_id = t.t_id
where st.s_id = s.s_id
and t.name = '王某');--2
select distinct st.s_id, st.s_name
from score s
left join student st
on st.s_id = s.s_id
where st.s_name != '李某'
and s.c_id in (select s2.c_id
from score s2
left join student st2
on s2.s_id = st2.s_id
where st2.s_name = '李某')