常用数据库

创建表语句

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] GOSET 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)；

查询语句

select * from dbo.stuscore

问题：

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

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