学子各门课程战绩总结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

问题:

 

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

图片 2图片 3

select name,SUM(score) as allscore from dbo.stuscore
group by name 
order by allscore;

View Code

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

图片 4图片 5

select stuid,name,SUM(score) as allscore from dbo.stuscore 
group by name,stuid 
order by allscore;

View Code

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

图片 6图片 7

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;

View Code

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

图片 8图片 9

select stuid,name,AVG(score) avgscore from dbo.stuscore 
group by stuid,name;

View Code

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

图片 10图片 11

select t1.stuid,t1.name,t1.subject,t1.score 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;

View Code

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

图片 12图片 13

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;

View Code

7.统计如下: 

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

图片 14图片 15

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)平均分 from stuscore
group by stuid,name order by 总分;

View Code

 

8.列出各门课程的平均成绩(要求显示字段:课程,平均成绩)

图片 16图片 17

select subject,AVG(score)平均成绩 from stuscore 
group by subject;

View Code

 

9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)

图片 18图片 19

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

View Code

 

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

图片 20图片 21

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

View Code

图片 22图片 23

 select t3.*  from (
 select top 100 percent stuid,name,subject,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score) 1 as 名次 from
 stuscore t2  where subject='数学' order by t2.score desc) t3 
 where t3.名次 between 2 and 3 order by t3.score desc;

View Code

图片 24图片 25

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

View Code

后面两个方法的不同之处可以参见:

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

图片 26图片 27

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='李四'

View Code

图片 28图片 29

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;

View Code

12.统计如下: 

课程 不及格(0-59)个 良(60-80)个 优(81-100)个
       

图片 30图片 31

 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 优秀 from stuscore
 group by subject;

View Code

13.统计如下:

 

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

 

图片 32图片 33

 declare @s nvarchar(1000)
 set @s=''
 select @s =@s ',' name '(' convert(nvarchar(10),score) '分)' from 
 stuscore where subject='数学'
 set @s=stuff(@s,1,1,' ')print '数学:' @s

View Code

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:学子各门课程战绩总结SQL语句大全

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。