点击(此处)折叠或打开
-
create table qsj_test(classid varchar(10),stu_id varchar(10),score int)
-
-
insert into qsj_test values('1班','学生2',50)
-
insert into qsj_test values('1班','学生3',0)
-
insert into qsj_test values('2班','学生4',90)
-
insert into qsj_test values('3班','学生5',90)
-
-
select b.classid ,b.stu_id,max from (select classid ,max(score) as max from qsj_test group by classid) a left join qsj_test b
- on (a.classid=b.classid and a.max=b.score)