sql查询成绩表中每一科成绩最高的分数以及这个学生的名字，班级，面试的时候碰到的问题

[qq_43089258]

请问这种情况要怎么查询.

[晴天8]

首先，你这个需求描述，就有点疑惑，每一科成绩最高的分数以及这个学生的名字..... 我根本没有看到科目相关字段啊，根据你的结果集； 你有点像是求不同性别的最高分吧？或者干脆就是求整个student的最高分对应的学生信息..班级信息等等

use tempdb;
if object_id('#student') is not null
	drop table #student
select '001' as id ,1 as c_id,'张三' as name,'男' as sex,90 as score
into #student
union all
select '002' as id ,1 as c_id,'李四' as name,'女' as sex,80 as score union all
select '003' as id ,1 as c_id,'王五' as name,'男' as sex,65 as score union all
select '004' as id ,2 as c_id,'小刘' as name,'男' as sex,60 as score union all
select '005' as id ,2 as c_id,'赵六' as name,'女' as sex,75 as score union all
select '006' as id ,2 as c_id,'李五' as name,'女' as sex,90 as score 

if object_id('#class') is not null
	drop table #class
select 1 as id,'一班' as name 
into #class
union all
select 2 as id,'二班' as name 

-- 如果是想获取 score表最大分数的学生信息.....SQL如下
select t2.name as c_name,t1.name,t1.sex,t1.score 
from #student  t1 
left join #class t2 
on  t1.c_id=t2.id
where score in (
	select max(score) from #student
)
order by t1.id

--
-- 如果是想获取 score表不同性别成绩最高的分数以及这个学生的信息...SQL如下
select t2.name as c_name,t1.name,t1.sex,t1.score 
from #student  t1 
left join #class t2 
on  t1.c_id=t2.id
join (select sex,max(score) as score from #student group by sex) t3  --如果你的需求真的是科目字段，这里group by 科目字段即可，select 中也是科目字段,max(score)
on t1.sex=t3.sex and t1.score=t3.score
order by t1.id

结果如下图

[晴天8]

上面那个例子有个地方的测试数据和你的不太一样，改成和你一样后，上面的代码运行结果和你想要的一摸一样

[笑着宣泄悲伤]

select c.name as c_name,s.name,s.sec,max(s.score) from class c inner join student s on s.c_id=c.id 不晓得对不对哎

[锟斤拷锟斤拷]

因为存在并列最高分的情况，所以还是先查出每科的最高分，然后再join班级表和成绩明细表比较好

[猫狸嘎]

3#的有问题，会提示‘选择列表中的列 'c.name' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。’ 下面的查询应该可以满足楼主

select
名字 = a1.name
,班级 = a2.name
from
student as a1
inner join class as a2 on a2.id = a1.c_Id
where a1.score = (select 最高分 = max(score) from student)

[duangufei]

select ISNULL( t2.Name,'') c_Name,t1.Name,t1.Sex,t1.Score from student t1 left join class t2 on t1.c_id=t2.id where Score=(select MAX(Score) from student)