java输出50次语句_一个项目涉及到的50个Sql语句 弄懂后做项目没压力了

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

--1.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数]

from Student a , SC b , SC c

where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno =

'01'

and c.Cno =

'02'

and b.score > c.score

--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数]

from Student a

left

join SC b on a.Sno = b.Sno and b.Cno =

'01'

left

join SC c on a.Sno = c.Sno and c.Cno =

'02'

where b.score >

isnull(c.score,0)

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

--2.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数]

from Student a , SC b , SC c

where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno =

'01'

and c.Cno =

'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.Sno = b.Sno and b.Cno =

'01'

left

join SC c on a.Sno = c.Sno and c.Cno =

'02'

where

isnull(b.score,0) < c.score