create table test

(id int,

name varchar(10),

score int,

classname varchar(20));

insert into test values (1,'张三',100,'一班');

insert into test values (2,'李四',89,'一班');

insert into test values (3,'王五',97,'一班');

insert into test values (4,'赵六',87,'二班');

insert into test values (5,'孙七',94,'二班');

insert into test values (6,'杨八',76,'二班');

select t.* from

(select test.*,row_number() over (partition by classname order by score desc) rn

from test) t

where rn=1;