帆软用户77r8vnVzDX(提问者)这样写有问题,只会求出一个班级的
写法一:
select b.class, b.students, b.km ,b.fs ,b.rk from (
select a.*,rank() over(partition by class order by fs desc) as rk from classa a
where km=\'数学\') b where b.rk=1;
写法二:
with temp as(
select a.*,rank() over(partition by class order by fs desc) as \"数学最好的学生\" from classa a
where km=\'数学\')
select * from temp where 数学最好的学生=1;