需求描述

查找出选修了所有课程的学生

准备数据

1.表
学生信息表：student
课程信息表：course
学生选课表：studentcourse
2.脚本
create table STUDENT
(
stuno INTEGER not null,
sname VARCHAR2(20),
gender VARCHAR2(5),
age INTEGER
);
create table COURSE
(
couno INTEGER not null,
cname VARCHAR2(20)
);
create table STUDENTCOURSE
(
stuno INTEGER not null,
couno INTEGER not null
);
insert into student (STUNO, SNAME, GENDER, AGE) values (200215121, ‘李勇’, ‘男’, 20);
insert into student (STUNO, SNAME, GENDER, AGE) values (200215122, ‘刘晨’, ‘女’, 19);
insert into student (STUNO, SNAME, GENDER, AGE) values (200215123, ‘王敏’, ‘女’, 18);
insert into student (STUNO, SNAME, GENDER, AGE) values (200215124, ‘徐明’, ‘男’, 17);
insert into course (COUNO, CNAME) values (1, ‘数据库’);
insert into course (COUNO, CNAME) values (2, ‘数学’);
insert into course (COUNO, CNAME) values (3, ‘信息系统’);
insert into course (COUNO, CNAME) values (4, ‘操作系统’);
insert into studentcourse (STUNO, COUNO) values (200215121, 1);
insert into studentcourse (STUNO, COUNO) values (200215121, 2);
insert into studentcourse (STUNO, COUNO) values (200215121, 3);
insert into studentcourse (STUNO, COUNO) values (200215121, 4);
insert into studentcourse (STUNO, COUNO) values (200215122, 1);
insert into studentcourse (STUNO, COUNO) values (200215122, 2);
insert into studentcourse (STUNO, COUNO) values (200215122, 3);
insert into studentcourse (STUNO, COUNO) values (200215122, 4);
insert into studentcourse (STUNO, COUNO) values (200215123, 3);
insert into studentcourse (STUNO, COUNO) values (200215124, 2);

SQL1:双重 not exists

select *
from student s
where not exists ( select 1
from course c
where not exists ( select 1
from studentcourse sc
where sc.stuno = s.stuno
and sc.couno = c.couno
)
);
数据库_SQL：双重 not exists 改 join

SQL2: join

select stuno, sname, age, gender from student
minus
select * from (
select
distinct a.stuno, a.sname, a.age, a.gender
from (
select
s.*, c.couno
from student s, course c
) a
left outer join studentcourse sc
on sc.stuno = a.stuno
and sc.couno = a.couno
where sc.stuno is null
)
数据库_SQL：双重 not exists 改 join

分析

SQL1:

可理解为三层 for 循环
for(第一层){
    从 student 表中拿出一条数据
    for(第二层){
        从 course 表中拿出一条数据
        for(第三层){
            从 studentcourse 表中拿出一条数据，所符合关联条件则返回 true
        }
    }
}

SQL2:

根据 studentcourse 表的数据，判断若 student 表中的某一条数据与 course 表中的所有数据都有关联关系的则为选项了所有课程的学生。
用如下SQL查出所有学生的选课情况
select
a.stuno, a.sname, a.couno, a.cname, sc.stuno, sc.couno
from (
select
s., c.
from student s, course c
) a
left outer join studentcourse sc
on sc.stuno = a.stuno
and sc.couno = a.couno
order by a.stuno, a.couno
数据库_SQL：双重 not exists 改 join
上图中红框内的为 “选修了所有课程的学生”。
从上面这个查询中把 “选修了所有课程的学生” 去掉，剩下的就是选修了部分课程以及没有选修任何课程的学，再从 student 表中将这部分学生去掉，剩下的即为 “选修了所有课程的学生” 。

数据库_SQL：双重 not exists 改 join