mysql计算每门课程的平均分_学生各门课程成绩统计SQL语句大全

转载

创建表

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [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_PADDING OFF

插入数据

insert into dbo.stuscore values ('张三','数学',89,1)；

insert into dbo.stuscore values ('张三','语文',80,1)；

insert into dbo.stuscore values ('张三','英语',70,1)；

insert into dbo.stuscore values ('李四','数学',90,2)；

insert into dbo.stuscore values ('李四','语文',70,2)；

insert into dbo.stuscore values ('李四','英语',80,2)；

查询结果显示，如下截图：

问题：

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

select name,SUM(score) as allscore fromdbo.stuscoregroup bynameorder by allscore;

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

select stuid,name,SUM(score) as allscore fromdbo.stuscoregroup byname,stuidorder by allscore;

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

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

(select stuid,max(score) as maxscore from stuscore group bystuid) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore;

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

select stuid,name,AVG(score) avgscore fromdbo.stuscoregroup by stuid,name;

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

select t1.stuid,t1.name,t1.subject,t1.score fromstuscore t1,(select subject,MAX(score) as maxscore from stuscore group bysubject)t2where t1.subject = t2.subject and t1.score = t2.maxscore;

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

select t1.* from stuscore t1 where t1.stuid in(select top 2 stuid from stuscore where subject = t1.subject order by score desc)order by t1.subject;

7.统计如下：

学号

姓名

语文

数学

英语

总分

平均分

select stuid 学号,name 姓名,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)总分,avg(score)平均分 fromstuscoregroup by stuid,name order by 总分;

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

select subject,AVG(score)平均成绩 fromstuscoregroup by subject;

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

selectstuid,name,score,

(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 fromstuscore t2where subject='数学' order by score desc;--注释：排序，比较大小，比较的次数+1 = 排名。

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

select t3.* from(select top 2 t2.* from(select top 3 stuid,name,subject,score from stuscore wheresubject= '数学' order by score desc) t2 order byt2.score) t3order by t3.score desc;

select t3.* from(select top 100 percentstuid,name,subject,score,

(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 fromstuscore t2where subject='数学' order by t2.score desc) t3where t3.名次 between 2 and 3 order by t3.score desc;

select t3.* from(selectstuid,name,subject,score,

(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 fromstuscore t2where subject='数学') t3where t3.名次 between 2 and 3 order by t3.score desc;

后面两个方法的不同之处可以参见：http://blog.csdn.net/wrm_nancy/article/details/17170115

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

declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp

select null,name,score,stuid from stuscore where subject='数学'

order by score desc declare @id int set @id=0;update @tmp set @id=@id+1,pm=@id select * from @tmp where name='李四'

select stuid,name,subject,score,(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as名次from stuscore t2 where subject='数学' and name = '李四' order by score desc;

12．统计如下：

课程

不及格(0-59)个

良(60-80)个

优(81-100)个

select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as不及格,sum(case when score between 60 and 80 then 1 else 0 end) as良,sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 fromstuscoregroup by subject;

13．统计如下：

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

declare @s nvarchar(1000)set @s=''

select @s =@s+','+name+'('+convert(nvarchar(10),score)+'分)' fromstuscorewhere subject='数学'

set @s=stuff(@s,1,1,' ')print '数学:'+@s