求每门课的第一名的简单sql语句

1. create table stu2
   
2. (
   
3. stu_name varchar(20),
   
4. lesson varchar(10),
   
5. stu_score tinyint unsigned
   
6. );
   
7. 
   
8. insert into stu2 values('张三','数学',90);
   
9. insert into stu2 values('张三','语文',50);
   
10. insert into stu2 values('张三','地理',40);
    
11. insert into stu2 values('李四','语文',55);
    
12. insert into stu2 values('李四','政治',45);
    
13. insert into stu2 values('王五','政治',30);
    
14. insert into stu2 values('jack','数学',88);
    
15. insert into stu2 values('jack','语文',67);
    
16. insert into stu2 values('jack','地理',77);

每门课的最高分
select lesson,max(stu_score) from stu2 group by lesson;

每门课的第一名
注意，这个是错的：
select stu_name,lesson,max(stu_score) from stu2 group by lesson;

而必须用子查询才行
将每门课的最高分做成一个字表。而后用这个字表去和原表求交集。

select R1.stu_name,R1.lesson,R1.stu_score
from stu2 R1,
(select lesson,max(stu_score) as max_stu_score from stu2 group by lesson) R2

where R1.lesson=R2.lesson and R1.stu_score=R2.max_stu_score;

或者：select * from (select * from stu2 order by stu_score desc)as k group by lesson;

结果如下：

1. +----------+--------+-----------+
   
2. | stu_name | lesson | stu_score |
   
3. +----------+--------+-----------+
   
4. | 张三     | 数学   |        90 |
   
5. | jack     | 语文   |        67 |
   
6. | 李四     | 政治   |        45 |
   
7. | jack     | 地理   |        77 |
   
8. +----------+--------+-----------+