尤益达

一个菜鸟程序猿的成长过程!

编程题(操作题)为实现以下要求编写出相应的SQL语句:

以下是学生成绩表,包含相应的四个字段(列名的中英文说明)与数据,学生成绩表(stuscore):

姓名:name         课程:subject                  分数:score         学号:stuid

张三            数学                  89               1

张三            语文                  80               1

张三            英语                  70               1

李四            数学                  90               2

李四            语文                  70               2

李四            英语                  80               2

a、计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)

select name,sum(score) as allscore

from stuscore

group by name

order by allscore

 

b、计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)

select distinct t1.name,t1.stuid,t2.allscore

from  stuscore t1,( select stuid,sum(score) as allscore

from stuscore group by stuid) t2

where t1.stuid=t2.stuid

order by t2.allscore desc

 

c、计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)

select t1.stuid,t1.name,t1.subject,t1.score

from stuscore t1,(select stuid,max(score) as maxscore

from stuscore group by stuid) t2

where t1.stuid=t2.stuid and t1.score=t2.maxscore

 

d、计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)

select distinct t1.stuid,t1.name,t2.avgscore

from stuscore t1,(select stuid,avg(score) as avgscore

from stuscore group by stuid) t2

where t1.stuid=t2.stuid

 

e、列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)

select  t1.stuid,t1.name,t1.subject,t2.maxscore

from stuscore t1,(select subject,max(score) as maxscore

from stuscore group by subject) t2

where t1.subject=t2.subject and t1.score=t2.maxscore

 

f、列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)

select distinct t1.*

from stuscore t1

where t1.id in (select top 2 stuscore.id

from stuscore where subject = t1.subject order by score desc)

order by t1.subject

 

g、统计如下:学号     姓名     语文     数学     英语     总分     平均分

select stuid as 学号,name as 姓名,

sum(case when subject=\’语文\’ then score else 0 end) as 语文,

sum(case when subject=\’数学\’ then score else 0 end) as 数学,

sum(case when subject=\’英语\’ then score else 0 end) as 英语,

sum(score) as 总分,(sum(score)/count(*)) as 平均分

from stuscoregroup by stuid,name order by 总分desc






发表评论:

«   2019年7月   »
1234567
891011121314
15161718192021
22232425262728
293031
控制面板
您好,欢迎到访网站!
  查看权限
搜索
最新留言
    文章归档
    友情链接

    Powered By Z-BlogPHP 1.5.2 Zero

    Copyright cjava.net All Rights Reserved 苏ICP备18017901号