SQL查找每科最高分和人

表student，有三个字段name、subject、score，找出某个科目的最高分和人。

例表

select s1.name,s2.subject,s2.score from student s1 join (select subject ,max(score) score from student group by subject ) s2 on s1.subject=s2.subject and s1.score=s2.score;

解析：

1、整体结构：Select 条件 from 表1 join 条件（整条语句） 表2 on 表1表2的连接条件

2、临时表s1 —student
临时表s2----Select subject,max(score) from student group by subject;//每科最高分表

3、在s1中查找s1和s2中分数和成绩相等的人
select s1.name,s2.subject,s2.score from student s1 join (select subject ,max(score) score from student group by subject ) s2 on s1.subject=s2.subject and s1.score=s2.score;