数据库子查询问题。请各位大牛赐教

Reita_anz 2012-06-10 05:01:40

S表 (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C表 (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC表 (SNO,CNO,SCGRADE) 选课关系表。SCGRADE 为成绩

问题：列出“1”号课成绩比“2”号课成绩高的所有学生的学号

大牛请赐教。

...全文

136 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

ldw2545398 2012-06-21

打赏
举报

select sno from (select t.SNO,t.SCGRADE-nvl(d.SCGRADE ) SCGRADE_c from (SELECT sc.sno, SCGRADE FROM sc where CNO=1) t,(SELECT sc.sno, SCGRADE FROM sc where CNO=2) d
where t.sno = d.sno(+)) t where SCGRADE_c>0

小德 2012-06-12

打赏
举报



CREATE TABLE s(SNO VARCHAR(10),SNAME VARCHAR2(20));

CREATE TABLE c(CNO VARCHAR(10),CNAME VARCHAR2(20),CTEACHER VARCHAR2(10));

CREATE TABLE sc(SNO VARCHAR(10),CNO VARCHAR(10),SCGRADE NUMBER(4));

INSERT INTO s VALUES ('s001','jack');

INSERT INTO s VALUES ('s002','rose');

INSERT INTO s VALUES ('s003','mike');



INSERT INTO c VALUES ('1','history','t01');

INSERT INTO c VALUES ('2','English','t02');



INSERT INTO sc VALUES ('s001','1',90);

INSERT INTO sc VALUES ('s001','2',80);

INSERT INTO sc VALUES ('s002','1',85);

INSERT INTO sc VALUES ('s002','2',90);

INSERT INTO sc VALUES ('s003','1',80);

INSERT INTO sc VALUES ('s003','2',60);



SELECT sno FROM SC t WHERE t.cno='1' AND EXISTS (SELECT 1 FROM SC WHERE sno=t.sno AND cno='2' AND scgrade<t.scgrade);



输出：

SNO

s001

s003

Reita_anz 2012-06-10

打赏
举报

SELECT sc.sno FROM sc
INNER JOIN (SELECT * FROM sc WHERE cno=2 AND sno IN(SELECT sno FROM sc WHERE cno=1)) xu
ON (xu.sno=sc.sno)
WHERE sc.cno=1 AND sc.sno IN(SELECT sno FROM sc WHERE cno=2)
AND sc.scgrade>xu.scgrade

Reita_anz 2012-06-10