您可以使用此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.