SQL查询每课成绩最高的学生的信息

大概思路是找出单科最高成绩，再跟成绩表关联找出学生。

假设成绩表有字段科目ID、学生ID、学生成绩三个字段

大概以下SQL，可以参考下：

select 科目ID、学生ID、学生成绩 b最高成绩 from 成绩表 a

left join

(select 科目ID，max(学生成绩) as 最高成绩 from 成绩表 group by 科目ID) b

on (a科目ID=b科目ID and a学生成绩=b最高成绩)

where b最高成绩 is not null;

---1 计算每个人的总成绩并排名（要求显示字段：姓名，总成绩）

select name,sum(cast(score as bigint)) as allscore from stuscore group by name order by allscore desc

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

select stuid,name,sum(cast(score as bigint)) as allscore from stuscore group by stuid,name order by allscore desc

---3 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

SELECT t1stuid,t1name,t1subject,t1score from stuscore t1,(SELECT stuid,max(score) as maxscore from stuscore group by stuid) t2 where t1stuid=t2stuid and t1score=t2maxscore

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

select distinct t1stuid,t1name,t2avgscore from stuscore t1,(select stuid,avg(cast(score as bigint)) as avgscore from stuscore group by stuid) t2 where t1stuid=t2stuid

---5 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

select t1stuid,t1name,t1subject,t2maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2 where t1subject=t2subject and t1score=t2maxscore

---6 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

select distinct t1 from stuscore t1 where t1stuid in(select top 2 stuscorestuid from stuscore where subject = t1subject order by score desc)order by t1subject

---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(cast(score as bigint)) as 总分,(sum(cast(score as bigint))/count()) as 平均分 from stuscore group by stuid,name order by 总分 desc

---8 列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

select subject,avg(cast(score as bigint)) as avgscore from stuscore group by subject

---9 列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

select from stuscore where subject ='数学' order by score desc

---10 列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

select t3 from(select top 2 t2 from (select top 3 name,subject,score,stuid from stuscore where subject='数学' order by score desc) t2 order by t2score) t3 order by t3score desc

---11 求出李四的数学成绩的排名

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 where name='李四'

---12 统计各科目及格人数

select subject,

(select count() from stuscore where score<60 and subject=t1subject) as 不及格,

(select count() from stuscore where score between 60 and 80 and subject=t1subject) as 良,

(select count() from stuscore where score >80 and subject=t1subject) as 优

from stuscore t1 group by subject

---13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

declare @s varchar(1000)

set @s=''

select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='数学'

set @s=stuff(@s,1,1,'')

print '数学:'+@s

新建查询，然后写语句

go

select 姓名,Max(分值) As 最高分 from 表名 group by 姓名;

这样就好了，不要想复杂了

1、用到两个函数：max(最高）和min（最低）。

2、查询语句如下：

select max(单分数)as 最高分数,min(单分数) as 最低分数 --单分数为列名

from TTABLE --表名

where 1=1 --条件区

SELECT childabc,childcource,aname

FROM  (select max(bpoint) as abc,ccource from `student` as a join `achievement` as b join `course` as c on asex = 1 and bsid=aid and bcid=cid group by ccource) as child

join `student` as a join `achievement` as b join `course` as c on asex = 1 and bsid=aid and bcid=cid  where childabc=bpoint and childcource=ccource

很繁琐,子查询和查询的都是同一个表同一个条件,答案包对

不要姓名要学号的话就把名字换一下

原理

子查询出最高分和科目,再用父查询把(同条件下)把最高分和科目配对

以上就是关于SQL查询每课成绩最高的学生的信息全部的内容，包括:SQL查询每课成绩最高的学生的信息、1查询成绩表的总分数，平均分，最低分和最高分。用sql语句怎么写、如何在MYSQL中筛选出每个人的最高分等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！