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;

 

版权声明：本文为jasonzeng888原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/jasonzeng888/p/6202630.html