sql统计－关于学生成绩

转载地址：http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.html

 

学生成绩表(stuscore)：

 

姓名：name

课程：subject

分数：score

学号：stuid

张三

数学

89

1

张三

语文

80

1

张三

英语

70

1

李四

数学

90

2

李四

语文

70

2

李四

英语

80

2

创建表
SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

SET ANSI_PADDINGON

GO

CREATETABLE[dbo].[stuscore](

   [name][varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

   [subject][varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

   [score][int]NULL,

   [stuid][int]NULL

)ON[PRIMARY]



GO

SET ANSI_PADDINGOFF

问题：

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.       计算每个人的总成绩并排名

select name,sum(score)as allscore from stuscore groupby nameorderby allscore

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

selectdistinct t1.name,t1.stuid,t2.allscorefrom  stuscore t1,

(

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

)t2

where t1.stuid=t2.stuid

orderby t2.allscoredesc

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

select t1.stuid,t1.name,t1.subject,t1.scorefrom stuscore t1,

(

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

) t2

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

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

selectdistinct t1.stuid,t1.name,t2.avgscorefrom stuscore t1,

(

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

) t2

where t1.stuid=t2.stuid

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

select  t1.stuid,t1.name,t1.subject,t2.maxscorefrom stuscore t1,

(

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

) t2

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

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

 

selectdistinct t1.*from stuscore t1

where t1.stuidin

(selecttop2 stuscore.stuidfrom stuscorewhere subject= t1.subjectorderby scoredesc)

orderby t1.subject

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

select stuidas 学号,name as 姓名,

sum(casewhen subject='语文'then scoreelse0end)as 语文,

sum(casewhen subject='数学'then scoreelse0end)as 数学,

sum(casewhen subject='英语'then scoreelse0end)as 英语,

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

from stuscore

groupby stuid,name

orderby 总分desc

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

select subject,avg(score)as avgscore from stuscore

groupby subject

9.列出数学成绩的排名

declare@tmptable(pmint,name varchar(50),scoreint,stuid int)

insertinto@tmpselectnull,name,score,stuidfrom stuscorewhere subject='数学'orderby score desc

declare@idint

set@id=0;

update@tmpset@id=@id+1,pm=@id

select*from@tmp

select  DENSE_RANK ()OVER(orderby scoredesc) as row,name,subject,score,stuidfrom stuscorewhere subject='数学'

orderby scoredesc

 

declare@tmptable(pmintidentity(1,1),namevarchar(50),scoreint,stuid int)
insertinto@tmp
select name,score,stuidfrom stuscorewhere subject='数学'orderby score desc
select*from@tmp

 

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

select t3.*from

(

selecttop2 t2.*from (

selecttop3 name,subject,score,stuidfrom stuscorewhere subject='数学'

orderby scoredesc

) t2orderby t2.score

) t3orderby t3.scoredesc

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

declare@tmptable(pmint,name varchar(50),scoreint,stuid int)

insertinto@tmpselectnull,name,score,stuidfrom stuscorewhere subject='数学'orderby score desc

declare@idint

set@id=0;

update@tmpset@id=@id+1,pm=@id

select*from@tmpwhere name='李四'

12. 课程不及格（-59）良（-80）优（-100）

select subject,

(selectcount(*)from stuscorewhere score<60and subject=t1.subject)as 不及格,

(selectcount(*)from stuscorewhere score between60and80and subject=t1.subject)as 良,

(selectcount(*)from stuscorewhere score >80and subject=t1.subject)as 优

from stuscore t1groupby subject

13. 数学:张三(50分),李四(90分),王五(90分),赵六(76分)

declare@svarchar(1000)

set@s=''

select@s=@s+','+name+'('+convert(varchar(10),score)+'分)'from stuscore where subject='数学'

set@s=stuff(@s,1,1,'')

print'数学:'+@s