在学生成绩信息表(StudScoreInfo)和学生信息表(StudInfo)中将求出的平均分按以下等级 输出。(包括学号,姓名,性别,出生日期,平均分,等级字段)
平均分 等级
90—100 ‘优秀’
80—90 ‘优良’
70—80 ‘一般’
60—70 ‘及格’
60以下‘不及格’
select s1.studno,s1.studname,s1.studsex,s1.studbirthday,avg(s2.studscore),
case when avg(s2.studscore) between 90 and 100 then '优秀'
when avg(s2.studscore) between 80 and 90 then '优良'
when avg(s2.studscore) between 70 and 80 then '一般'
when avg(s2.studscore) between 60 and 70 then '及格'
else
'不及格'
end as ll
from studinfo s1,studscoreinfo s2
where s1.studno=s2.studno
group by s1.studno,s1.studname,s1.studsex,s1.studbirthday