注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

每日聊的博客

 
 
 

日志

 
 
 
 

学生成绩表(stuscore)分析  

2014-10-29 14:01:35|  分类: SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

学生成绩表(stuscore):

姓名:name

课程:subject

分数:score

学号:stuid

张三

数学

89

1

张三

语文

80

1

张三

英语

70

1

李四

数学

90

2

李四

语文

70

2

李四

英语

80

2

学生成绩表(stuscore)分析 - li70154 - li70154的博客创建表

学生成绩表(stuscore)分析 - li70154 - li70154的博客

问题:

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分)


create TABLE stuscore(
name varchar(20)
,subject  varchar(20)
,score  int
,stuid  int
);

insert into stuscore values('张三','数学',89,1);
insert into stuscore values('张三','语文',80,1);
insert into stuscore values('张三','英语',70,1);
insert into stuscore values('李四','数学',90,2);
insert into stuscore values('李四','语文',70,2);
insert into stuscore values('李四','英语',80,2);
insert into stuscore values('王五','数学',90,3);
insert into stuscore values('王五','语文',90,3);
insert into stuscore values('王五','英语',80,3);


答案:

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select name,sum(score) as allscore from stuscore group by name order by allscore
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select distinct t1.name,t1.stuid,t2.allscore from  stuscore t1,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客    
select stuid,sum(score) as allscore from stuscore group by stuid
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客)t2
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
where t1.stuid=t2.stuid
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
order by t2.allscore desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客 select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select stuid,max(score) as maxscore from stuscore group by stuid
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客) t2
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
where t1.stuid=t2.stuid and t1.score=t2.maxscore
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select stuid,avg(score) as avgscore from stuscore group by stuid
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客) t2
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
where t1.stuid=t2.stuid
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select  t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select subject,max(score) as maxscore from stuscore group by subject
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客) t2
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
where t1.subject=t2.subject and t1.score=t2.maxscore
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select distinct t1.* from stuscore t1 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
where t1.stuid in 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
select top 2 stuscore.stuid from stuscore where subject = t1.subject order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
order by t1.subject


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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select stuid as 学号,name as 姓名,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
sum(case when subject='语文' then score else 0 endas 语文,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
sum(case when subject='数学' then score else 0 endas 数学,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
sum(case when subject='英语' then score else 0 endas 英语,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
sum(score) as 总分,(sum(score)/count(*)) as 平均分
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
from stuscore
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
group by stuid,name 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
order by 总分desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select subject,avg(score) as avgscore from stuscore
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
group by subject
学生成绩表(stuscore)分析 - li70154 - li70154的博客

9.列出数学成绩的排名

学生成绩表(stuscore)分析 - li70154 - li70154的博客declare @tmp table(pm int,name varchar(50),score int,stuid int)
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
declare @id int
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
set @id=0;
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
update @tmp set @id=@id+1,pm=@id
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select * from @tmp 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客

 

学生成绩表(stuscore)分析 - li70154 - li70154的博客select  DENSE_RANK () OVER(order by score descas row,name,subject,score,stuid from stuscore where subject='数学'
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客

学生成绩表(stuscore)分析 - li70154 - li70154的博客declare @tmp table(pm int identity(1,1),name varchar(50),score int,stuid int)
学生成绩表(stuscore)分析 - li70154 - li70154的博客
insert into @tmp 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select name,score,stuid from stuscore where subject='数学' order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select * from @tmp

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客select t3.*  from
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select top 2 t2.*  from (
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select top 3 name,subject,score,stuid from stuscore where subject='数学'
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客) t2 
order by t2.score
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客) t3 
order by t3.score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客

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

学生成绩表(stuscore)分析 - li70154 - li70154的博客declare @tmp table(pm int,name varchar(50),score int,stuid int)
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score desc
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
declare @id int
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
set @id=0;
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
update @tmp set @id=@id+1,pm=@id
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select * from @tmp where name='李四'
学生成绩表(stuscore)分析 - li70154 - li70154的博客

12. 课程    不及格(-59   良(-80   优(-100
学生成绩表(stuscore)分析 - li70154 - li70154的博客select subject, 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
select count(*from stuscore where score<60 and subject=t1.subject) as 不及格,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
select count(*from stuscore where score between 60 and 80 and subject=t1.subject) as 良,
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客(
select count(*from stuscore where score >80 and subject=t1.subject) as 优
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
from stuscore t1 group by subject
学生成绩表(stuscore)分析 - li70154 - li70154的博客
13. 数学:张三(50),李四(90),王五(90),赵六(76)
学生成绩表(stuscore)分析 - li70154 - li70154的博客declare @s varchar(1000)
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
set @s=''
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='数学' 
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
set @s=stuff(@s,1,1,'')
学生成绩表(stuscore)分析 - li70154 - li70154的博客
学生成绩表(stuscore)分析 - li70154 - li70154的博客
print '数学:'+@s
学生成绩表(stuscore)分析 - li70154 - li70154的博客
  评论这张
 
阅读(14)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017