例表

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;