mysql 更新为最大值_Mysql用group by更新最大值

您可以使用此SELECT查询返回您需要的relative_order：

SELECT

t1.id,

t1.grade,

COUNT(t2.id) relative_order

FROM

yourtable t1 INNER JOIN yourtable t2

ON t1.grade=t2.grade AND t1.id>=t2.id

GROUP BY

t1.id,

t1.grade

或者如果要更新值,可以使用上一个查询加入表,如下所示：

UPDATE

yourtable INNER JOIN (

SELECT

t1.id,

t1.grade,

COUNT(t2.id) relative_order

FROM

yourtable t1 INNER JOIN yourtable t2

ON t1.grade=t2.grade AND t1.id>=t2.id

GROUP BY

t1.id,

t1.grade) seq

ON yourtable.id=seq.id AND yourtable.grade=seq.grade

SET

yourtable.relative_order = seq.relative_order

请参阅小提琴here.