学⽣各门课程成绩统计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 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 top2 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 else0end )as语⽂,
sum(case when subject='数学'then score else0end )as数学,
sum(case when subject='英语'then score else0end )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)+1as名次from stuscore t2
where subject='数学'order by score desc;
--注释：排序，⽐较⼤⼩，⽐较的次数+1 = 排名。
View Code
10．列出数学成绩在2-3名的学⽣（要求显⽰字段：学号，姓名，科⽬，成绩）
select t3.*from (
select top2  t2.*from (
select top3 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 top100percent stuid,name,subject,score,
(select count(*) from stuscore t1 where subject ='数学'and t1.score > t2.score)+1as名次from
stuscore t2  where subject='数学'order by t2.score desc) t3
where t3.名次between2and3order 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)+1as名次from
sql查询面试题及答案stuscore t2  where subject='数学') t3
where t3.名次between2and3order 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)+1as名次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 between0and59then1else0end) as不及格,
sum(case when score between60and80then1else0end) as良,
sum(case when score between81and100then1else0end) as优秀from stuscore
group by subject;
View Code
13．统计如下：
数学: 张三(50分)，李四(90分)，王五(90分)，赵六(76分)
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。