多表分类查询
人员表teacher字段为name，lessonId，position；职称认证课程表attestation字段为position，lessonId，lessonPassScore；认证课程考试结果表result字段为lessonId，score当人员的职位所对应的的所有课程都通过时才能得到认证（一个name只有一个position但一个position有多个lessonId需要通过）。怎样用sql实现？
oracle查询
[解决办法]
大致思路是
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 professor
8 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 70
SQL> 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 100
8 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
rose
SQL>
