加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

MySQL_关于学生成绩

(2013-12-13 23:06:15)

sql统计－关于学生成绩

 

学生成绩表(stuscore)：

姓名：name

课程：subject

分数：score

学号：stuid

张三

数学

89

1

张三

语文

80

1

张三

英语

70

1

李四

数学

90

2

李四

语文

70

2

李四

英语

80

2

http://www.cnblogs.com/Images/OutliningIndicators/None.gif

问题：

1.    计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

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

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

4.    计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

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

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

7.    统计如下：

学号

姓名

语文

数学

英语

总分

平均分

 

 

 

 

 

 

 

 

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

11．求出李四的数学成绩的排名

12．统计如下：

课程

不及格（0-59）个

良（60-80）个

优（81-100）个

 

 

 

 

  

 

13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

答案：

1.       计算每个人的总成绩并排名

2.   计算每个人的总成绩并排名

http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect distinct t1.name,t1.stuid,t2.allscore from  stuscore t1,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif    select stuid,sum(score) as allscore from stuscore group by stuid
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif)t2
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifwhere t1.stuid=t2.stuid
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.giforder by t2.allscore desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

3. 计算每个人单科的最高成绩

4.计算每个人的平均成绩

5.列出各门课程成绩最好的学生

http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect  t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect subject,max(score) as maxscore from stuscore group by subject
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif) t2
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifwhere t1.subject=t2.subject and t1.score=t2.maxscore
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

6.列出各门课程成绩最好的两位学生


7.学号    姓名    语文     数学     英语     总分  平均分

http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect stuid as 学号,name as 姓名,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifsum(case when subject='语文' then score else 0 end) as 语文,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifsum(case when subject='数学' then score else 0 end) as 数学,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifsum(case when subject='英语' then score else 0 end) as 英语,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifsum(score) as 总分,(sum(score)/count(*)) as 平均分
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.giffrom stuscore
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifgroup by stuid,name 
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.giforder by 总分desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

8.列出各门课程的平均成绩

9.列出数学成绩的排名

http://www.cnblogs.com/Images/OutliningIndicators/None.gifdeclare @tmp table(pm int,name varchar(50),score int,stuid int)
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifinsert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifdeclare @id int
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifset @id=0;
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifupdate @tmp set @id=@id+1,pm=@id
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect * from @tmp 
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

 


http://www.cnblogs.com/Images/OutliningIndicators/None.gifdeclare @tmp table(pm int identity(1,1),name varchar(50),score int,stuid int)
http://www.cnblogs.com/Images/OutliningIndicators/None.gifinsert into @tmp 
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect name,score,stuid from stuscore where subject='数学' order by score desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect * from @tmp

10. 列出数学成绩在2-3名的学生

http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect t3.*  from
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect top 2 t2.*  from (
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect top 3 name,subject,score,stuid from stuscore where subject='数学'
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.giforder by score desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif) t2 order by t2.score
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif) t3 order by t3.score desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

11. 求出李四的数学成绩的排名

http://www.cnblogs.com/Images/OutliningIndicators/None.gifdeclare @tmp table(pm int,name varchar(50),score int,stuid int)
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifinsert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score desc
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifdeclare @id int
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifset @id=0;
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifupdate @tmp set @id=@id+1,pm=@id
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect * from @tmp where name='李四'
http://www.cnblogs.com/Images/OutliningIndicators/None.gif

12. 课程    不及格（-59）   良（-80）   优（-100）
http://www.cnblogs.com/Images/OutliningIndicators/None.gifselect subject, 
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.gif(select count(*) from stuscore where score >80 and subject=t1.subject) as 优
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
http://www.cnblogs.com/Images/OutliningIndicators/None.giffrom stuscore t1 group by subject
http://www.cnblogs.com/Images/OutliningIndicators/None.gif
13. 数学:张三(50分),李四(90分),王五(90分),赵六(76分)

 

本文来源：http://blog.csdn.net/chamtianjiao/article/details/6863013

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有