mysql 实现max partition by x order by x

oracle sql：select a.*,max(val) over(partition by x1 order by x2) from table a 

测试用例：

select t.*,max(score) over (partition by id order by calss) max
from T_SCORE t

数据

 

mysql实现：

-- 通过判断 class 是否>=取到的class 实现
select a.*,
case when a.calss<b.calss then a.score else b.score end max
from t_score a
left join (
-- 这一层是取到按id分组的最大值所在的最小的class 
select a.id,min(a.calss) calss,b.score
from t_score a
left join (select id,max(score) score from t_score group by id) b 
on a.id=b.id
where a.score=b.score
group by a.id,b.score
) b 
on a.id=b.id
order by a.id,a.calss

比较麻烦，需要至少3次关联，欢迎补充