MySQL Workbench/MariaDB：按score降序排序后重编num列序号

嗨，刚好我之前处理过类似的需求，不用存储过程或函数完全可以搞定，分两种场景给你方案：

场景1：仅查询展示，不修改原表 #

如果只是想在查询结果里看到排序后的num列（001、002...），直接用窗口函数ROW_NUMBER()配合LPAD()格式化就行，MySQL 8.0+和MariaDB 10.2+都支持这个写法：

SELECT
    LPAD(ROW_NUMBER() OVER (ORDER BY score DESC), 3, '0') AS num,
    score,
    -- 把你表中其他需要展示的字段都列在这里
    name,
    class
FROM your_table
ORDER BY score DESC;

• ROW_NUMBER() OVER (ORDER BY score DESC)：按score降序生成1、2、3...的连续序号
• LPAD(..., 3, '0')：把序号补成3位长度，不足的前面加0，比如1变成001，10变成010

场景2：更新原表的num列 #

如果要把这个编号直接写入原表的num字段，需要结合子查询或CTE来定位每行数据（假设你的表有主键id用来唯一标识行）：

方式1：用CTE（推荐，语法更清晰） #

WITH ranked_data AS (
    SELECT
        id,
        LPAD(ROW_NUMBER() OVER (ORDER BY score DESC), 3, '0') AS new_num
    FROM your_table
)
UPDATE your_table t
JOIN ranked_data rd ON t.id = rd.id
SET t.num = rd.new_num;

方式2：兼容旧版本数据库（不支持窗口函数时用） #

如果你的MySQL/MariaDB版本比较老，不支持窗口函数，可以用用户变量来实现：

UPDATE your_table t
JOIN (
    SELECT
        id,
        LPAD(@row_num := @row_num + 1, 3, '0') AS new_num
    FROM your_table, (SELECT @row_num := 0) r
    ORDER BY score DESC
) rd ON t.id = rd.id
SET t.num = rd.new_num;

补充说明 #

如果存在多个行score相同的情况，ROW_NUMBER()会给它们分配不同的序号（即使分数一样）。如果想要相同分数的行共用同一个编号，可以把ROW_NUMBER()换成RANK()或DENSE_RANK()：

• RANK()：相同分数会跳过后续序号（比如两个001，下一个是003）
• DENSE_RANK()：相同分数共用序号，后续序号连续（两个001，下一个是002）

根据你的需求选择对应的函数就好啦！

内容的提问来源于stack exchange，提问作者Freddy19