Select distinct a.姓名, a.科目, a.分数 as 最高分 from {表A} a inner join (Select 科目, Max(分数) as 最高分 from {表A} group by 科目) b on a.科目= b.科目 and a.分数 = b.最高分
照着写出区分科目和班级的,就很小菜了:
Select distinct a.姓名,a.班级,a.科目, a.分数 as 最高分 from {表A} a inner join (Select 班级,科目, Max(分数) as 最高分 from {表A} group by 班级,科目) b on a.班级 = b.班级 and a.科目= b.科目 and a.分数 = b.最高分