写出sql语句取出每门功课成绩最好的学⽣数据（⾯试题）在笔试中⼀般都会碰到书写sql语句的题⽬，上次去⾯试就碰到了，所以在这⾥总结⼀些。
创建表语句
1SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stuscore]
2( [name] [varchar](50)  COLLATE Chinese_PRC_CI_AS  NULL,
3[subject] [varchar](50)  COLLATE Chinese_PRC_CI_AS  NULL,
4[score] [int]  NULL,
5[stuid] [int]    NULL)
6ON [PRIMARY]
7GO
8SET ANSI_PADDING OFF
多表查询sql语句面试题
插⼊数据
1insert into dbo.stuscore values ('张三','数学',89,1)；
2insert into dbo.stuscore values ('张三','语⽂',80,1)；
3insert into dbo.stuscore values ('张三','英语',70,1)；
4insert into dbo.stuscore values ('李四','数学',90,2)；
5insert into dbo.stuscore values ('李四','语⽂',70,2)；
6insert into dbo.stuscore values ('李四','英语',80,2)；
查询语句
select * from dbo.stuscore
问题：
1.计算每个⼈的总成绩并排名(要求显⽰字段：姓名，总成绩)
1select name,SUM(score) as allscore from dbo.stuscore
2group by name
3order by allscore;
2.计算每个⼈的总成绩并排名(要求显⽰字段: 学号，姓名，总成绩)
1select stuid,name,SUM(score) as allscore from dbo.stuscore
2group by name,stuid
3order by allscore;
3.计算每个⼈单科的最⾼成绩(要求显⽰字段: 学号，姓名，课程，最⾼成绩)
1select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
2(select stuid,max(score) as maxscore from stuscore group by stuid) t2
3where t1.stuid=t2.stuid and t1.score=t2.maxscore;
4.计算每个⼈的平均成绩（要求显⽰字段: 学号，姓名，平均成绩）
1select stuid,name,AVG(score) avgscore from dbo.stuscore
2group by stuid,name;
5.列出各门课程成绩最好的学⽣(要求显⽰字段: 学号，姓名，科⽬，成绩)
1select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(
2select subject,MAX(score) as maxscore from stuscore group by subject)t2
3where t1.subject = t2.subject and t1.score = t2.maxscore;
6.列出各门课程成绩最好的两位学⽣(要求显⽰字段: 学号，姓名，科⽬，成绩)
1select  t1.* from stuscore t1 where t1.stuid in (
2select top 2 stuid from stuscore where subject = t1.subject order by score desc)
3order by t1.subject;
9．列出数学成绩的排名（要求显⽰字段：学号，姓名，成绩，排名）
1select stuid,name,score,
2(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2  3where subject='数学' order by score desc;

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