34,588
社区成员
发帖
与我相关
我的任务
分享
create table student (sid varchar(10),sname varchar(10),sage varchar(10),ssex varchar(10))
create table Sc (sid varchar(10),cid varchar(10),score int) --这个表不符合范式,各位可以理解再加上一个学期列
insert into sc select '0001','001',80
insert into sc select '0001','001',90 --加上学期列,这个数据就没有问题了
insert into sc select '0001','002',70
insert into sc select '0001','003',85
insert into sc select '0002','001',50
insert into sc select '0003','001',60
insert into sc select '0003','002',70
insert into sc select '0004','001',60
insert into sc select '0004','002',70
insert into sc select '0004','003',80
insert into sc select '0004','004',90
insert into student select '0001','swichin','26','男'
insert into student select '0002','lamh','27','男'
insert into student select '0003','ballack','34','男'
insert into student select '0004','lucio','32','男'
SELECT distinct sid FROM SC A
WHERE NOT EXISTS
(SELECT * FROM SC B WHERE B.sid='0001'
AND NOT EXISTS
(SELECT * FROM SC C WHERE C.cid=B.cid AND C.sid=A.sid))
and sid!='0001'