select a.* from S a join SC b on a.SNO=b.SNO join SC c on a.SNO=c.SNO and b.CNO=1 and c.CNO=2 where b.SCGRADE>c.SCGRADE
[Quote=引用楼主 hello345t 的回复:] 三张表:S(SNO, SNAME), C(CNO, CNAME, CTEACHER), SC(SNO, CNO, SCGRADE) 查询出每位学生课程号 为1的成绩 大于课程号为2的成绩的学生姓名? [/Quote]--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 --2.1、查询同时存在"01"课程和"02"课程的情况 select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score --2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况 select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a left join SC b on a.S# = b.S# and b.C# = '01' left join SC c on a.S# = c.S# and c.C# = '02' where isnull(b.score,0) < c.score
where b.SCGRADE>c.SCGRADE
三张表:S(SNO, SNAME), C(CNO, CNAME, CTEACHER), SC(SNO, CNO, SCGRADE)
查询出每位学生课程号 为1的成绩 大于课程号为2的成绩的学生姓名?
[/Quote]--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c
where a.S# = b.S# and a.S# = c.S# and b.C# = '01' and c.C# = '02' and b.score < c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a
left join SC b on a.S# = b.S# and b.C# = '01'
left join SC c on a.S# = c.S# and c.C# = '02'
where isnull(b.score,0) < c.score
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html