人员表teacher字段为name,lessonId,position;职称认证课程表attestation字段为position,lessonId,lessonPassScore;认证课程考试结果表result字段为lessonId,score当人员的职位所对应的的所有课程都通过时才能得到认证(一个name只有一个position但一个position有多个lessonId需要通过)。怎样用sql实现?
oracle查询
oracle查询
解决方案 »
- select aaa as t from ... 和select aaa t from ...有什么区别?
- Oracle下数值转时间
- oracle跨版本导入导出问题
- 发了几天都没人能解决,请好心人帮一下忙
- ORACLE9i中文版安裝文件,3CD的ISO光盤鏡像安裝不了,請高手指點,有分相送。
- 能否动态启动数据库???
- 诚请oracal8i高手给小弟引路 ,进者有分
- 如何把一个数据库的表和结构复制到另一个数据库,我用的是9i
- 数据表导出问题
- Create directory xmldir 問題
- sql求助,找出同一单位日期最大的一条记录,且单位只能出一次
- oracle 传值的 sql语句怎么写?????
1、先求出有课程不通过的人员
2、然后NOT IN
SQL> select * from teacher order by 1;NAME LESSONID POSITION
---------- ---------- ---------------
jack 1 professor
jack 3 professor
jack 2 professor
rose 2 vice_professor
rose 1 vice_professor
tom 3 professor
tom 2 professor
tom 1 professor8 rows selected.SQL> select * from attestation order by 1;POSITION LESSONID LESSONPASSSCORE
--------------- ---------- ---------------
professor 3 60
professor 2 80
professor 1 80
vice_professor 1 70
vice_professor 2 70SQL> select * from result order by 1;NAME LESSONID SCORE
---------- ---------- ----------
jack 1 100
jack 3 10
jack 2 100
rose 2 100
rose 1 100
tom 3 100
tom 2 100
tom 1 1008 rows selected.SQL> select distinct name from teacher where name not in
2 (
3 select result.name from
4 (
5 select t.*,a.lessonpassscore from teacher t,attestation a
6 where
7 t.lessonid = a.lessonid and t.position = a.position
8 )
9 temp,result
10 where
11 temp.name = result.name and temp.lessonid = result.lessonid and result.score < temp.lessonpassscore
12 );NAME
----------
tom
roseSQL>
2、然后NOT IN