有三张表：class、student、score 看了很多的博客，结果与问题总是差！！也不给截图 我自己写了一个，但还不够精简，还望那位大神还修改！代码如下：在最后面

1、班级表 class：

2、学生表 student：

3、分数表 score：

**1、查询各班各科分数最高的学生学号，姓名，班级名称，科目名称，分数**：

MySQL> create table ab_all_cl_st_sc as select st.stuid as 学号 , st.stuname as 姓名 , l.classname as 班级, sc.course as 科目, sc.score as 成绩 from ab_class cl , ab_student st , ab_score sc where cl.classid=st.classid and st.stuid=sc.stuid

select * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='一班' and tt.科目='语文')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='一班' and tt.科目='数学')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='一班' and tt.科目='英语')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='二班' and tt.科目='语文')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='二班' and tt.科目='数学')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='二班' and tt.科目='英语')union allselect * from AB_ALL_CL_ST_SC t where t.科目='语文' and t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='三班' and tt.科目='语文')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='三班' and tt.科目='数学')union allselect * from AB_ALL_CL_ST_SC t where t.成绩=(select max(tt.成绩) from AB_ALL_CL_ST_SC tt where tt.班级='三班' and tt.科目='英语')

结果：

如下代码效果不佳

select max(成绩),班级,科目 from AB_ALL_CL_ST_SC t group by 班级,科目

结果

修改颜色