写出sql语句取出每门功课成绩最好的学生数据(面试题) |
您所在的位置:网站首页 › sql查询各科最高分 › 写出sql语句取出每门功课成绩最好的学生数据(面试题) |
在笔试中一般都会碰到书写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);查询语句 select * from dbo.stuscore 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 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;9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名) 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;更全面的请点击:https://blog.csdn.net/javakakayi/article/details/65628024?utm_source=copy
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |