drop table stu_cour;
drop table student;
drop table course;create table student(
sid char(10) constraint student_sid_pk primary key, --学号
sname varchar(20) not null --姓名
);create table course(
cid char(10) constraint course_cid_pk primary key, --学号
cname varchar(20) not null --班级名
);create table stu_cour(
sid char(10) constraint stu_cour_sid_fk references student(sid),
cid char(10) constraint stu_cour_cid_fk references course(cid),
score number(4,1) constraint stu_cour_score_ck check (score between 0 and 100),
constraint stu_cour_sid_cid_pk primary key (sid,cid)
);drop sequence s1;create sequence s1
start with 1001
increment by 1;drop sequence s2;create sequence s2
start with 1001
increment by 1;insert into student(sid, sname) values ('s'||s1.nextval,'a');
insert into student(sid, sname) values ('s'||s1.nextval,'b');
insert into student(sid, sname) values ('s'||s1.nextval,'c');insert into course(cid, cname) values ('c'||s2.nextval, 'c1');
insert into course(cid, cname) values ('c'||s2.nextval, 'c2');
insert into course(cid, cname) values ('c'||s2.nextval, 'c3');insert into stu_cour(sid, cid, score) values ('s1001', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1001', 'c1002', 80);
insert into stu_cour(sid, cid, score) values ('s1001', 'c1003', 80);insert into stu_cour(sid, cid, score) values ('s1002', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1002', 'c1002', 80);insert into stu_cour(sid, cid, score) values ('s1003', 'c1001', 90);
insert into stu_cour(sid, cid, score) values ('s1003', 'c1002', 80);
insert into stu_cour(sid, cid, score) values ('s1003', 'c1003', 80);
commit;经典的3张表。
想要知道 b 没选哪些课程(课程名)
用外连接实现select c.cid
from course c left join stu_cour sc
on c.cid = sc.cid
and sc.sid = ('s1002') --对
where sc.sid is null and sc.cid is null;
这样可以实现select c.cid
from course c left join stu_cour sc
on c.cid = sc.cid
and (sc.sid = (select sid
from student
where sname = 'b')) -- 错了
where sc.sid is null and sc.cid is null;
但将其换成子查询为啥就不可以了？子查询的结果是's1002'啊各个子句的执行顺序不是 from --> and --> on --> where --> select 么？
--应该改为in就可以了，因你的sc.sid为一条记录，不能等于一个记录集，只能是存在于(in)
select c.cid
from course c left join stu_cour sc
on c.cid = sc.cid
and (sc.sid in (select sid
from student
where sname = 'b'))
where sc.sid is null and sc.cid is null;