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

  1. create table stu2
  2. (
  3. stu_name varchar(20),
  4. lesson varchar(10),
  5. stu_score tinyint unsigned
  6. );

  7. insert into stu2 values('张三','数学',90);
  8. insert into stu2 values('张三','语文',50);
  9. insert into stu2 values('张三','地理',40);
  10. insert into stu2 values('李四','语文',55);
  11. insert into stu2 values('李四','政治',45);
  12. insert into stu2 values('王五','政治',30);
  13. insert into stu2 values('jack','数学',88);
  14. insert into stu2 values('jack','语文',67);
  15. 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. +----------+--------+-----------+

展开阅读全文

没有更多推荐了,返回首页