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

``````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 from dbo.stuscore
group by name
order by allscore;
``````

View Code

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)  ``````select stuid,name,SUM(score) as allscore from dbo.stuscore
group by name,stuid
order by allscore;
``````

View Code

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)  ``````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.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）  ``````select stuid,name,AVG(score) avgscore from dbo.stuscore
group by stuid,name;
``````

View Code

5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)  ``````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.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)  ``````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.统计如下：

 学号 姓名 语文 数学 英语 总分 平均分  ``````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．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）  ``````select subject,AVG(score)平均成绩 from stuscore
group by subject;
``````

View Code

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）  ``````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名的学生（要求显示字段：学号，姓名,科目，成绩）  ``````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  `````` 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  `````` 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．求出李四的数学成绩的排名  ``````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  ``````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）个  `````` 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．统计如下：  `````` 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

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