数据库脚本面试题1

写出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;

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

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

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;

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

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

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;

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,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2  
where subject='数学' order by score desc;