1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
答案：select name,sum(score) as allscore from stuscore group by name order by allscore
2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
答案：select distinct t1.name,t1.stuid,from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=by desc
3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
答案：select t1.stuid,t1.name,t1.subject,from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=and t1.score=
4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
答案：select distinct t1.stuid,t1.name,from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=
5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
答案：select t1.stuid,t1.name,t1.subject,from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=and t1.score=
6.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
答案：select distinct t1.* from stuscore t1 where in (select top 2 from stuscore where subject = order by score desc) order by
7.统计如下：学号 姓名 语文 数学 英语 总分 平均分
答案：select stuid as 学号,name as 姓名,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) as 总分,(sum(score)/count(*)) as 平均分from stuscoregroup by stuid,name order by 总分desc
8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
答案：select subject,avg(score) as avgscore from stuscoregroup by subject
9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
答案：
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#软件测试面试##软件测试##软件测试#
答案：select name,sum(score) as allscore from stuscore group by name order by allscore
2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
答案：select distinct t1.name,t1.stuid,from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=by desc
3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
答案：select t1.stuid,t1.name,t1.subject,from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=and t1.score=
4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
答案：select distinct t1.stuid,t1.name,from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=
5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
答案：select t1.stuid,t1.name,t1.subject,from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=and t1.score=
6.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
答案：select distinct t1.* from stuscore t1 where in (select top 2 from stuscore where subject = order by score desc) order by
7.统计如下：学号 姓名 语文 数学 英语 总分 平均分
答案：select stuid as 学号,name as 姓名,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) as 总分,(sum(score)/count(*)) as 平均分from stuscoregroup by stuid,name order by 总分desc
8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
答案：select subject,avg(score) as avgscore from stuscoregroup by subject
9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
答案：
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#软件测试面试##软件测试##软件测试#
