如何在MySQL中排序后为Ordered字段更新增量值？

嘿，我来帮你搞定这个问题！你遇到的核心问题是没有把排序后的行和原表的记录正确关联起来，导致直接用WHERE子句时所有行拿到了同一个排名值。下面给你两种可行的解决方案，适配不同版本的MySQL：

方法一：使用窗口函数（MySQL 8.0及以上版本推荐） #

这种方法更清晰易读，利用CTE（公共表表达式）先生成每个记录的排序排名，再关联原表更新：

WITH ranked_users AS (
    SELECT 
        ID,
        -- 按LastName升序，再FirstName升序生成排名
        ROW_NUMBER() OVER (ORDER BY LastName ASC, FirstName ASC) AS row_rank
    FROM your_table_name -- 替换成你的实际表名
)
UPDATE your_table_name t
JOIN ranked_users r ON t.ID = r.ID
SET t.Ordered = r.row_rank;

原理说明： #

1. ranked_users CTE会先按LastName和FirstName的字母顺序排序，给每一行分配一个递增的排名值row_rank
2. 通过ID将原表和CTE关联，把对应的排名值更新到Ordered字段

方法二：使用用户变量（兼容MySQL 5.x版本） #

如果你的MySQL版本不支持窗口函数，可以用用户变量来跟踪递增的排名：

-- 初始化排名变量为0
SET @rank = 0;

-- 先按指定顺序排序，再逐行更新Ordered字段
UPDATE your_table_name
SET Ordered = (@rank := @rank + 1)
ORDER BY LastName ASC, FirstName ASC;

原理说明： #

1. 先设置变量@rank初始值为0
2. UPDATE语句会先按LastName和FirstName排序所有行，然后逐行执行更新，每更新一行就把@rank加1并赋值给Ordered，这样自然得到递增的排序值

验证结果 #

执行完上述任意一种方法后，执行查询：

SELECT ID, LastName, FirstName, Ordered FROM your_table_name;

就能得到你预期的结果：

ID | LastName | FirstName | Ordered
1 | Smith | John | 3
2 | Smith | Larry | 4
3 | Jones | Fred | 2
4 | Johnson | Todd | 1

为什么之前的WHERE子句无效？ #

你之前尝试的WHERE子句可能没有正确关联当前行和排序后的位置，比如直接使用不带关联的子查询（例如UPDATE table SET Ordered = (SELECT COUNT(*) FROM table WHERE ...)），这种情况下子查询只会返回一个固定值，导致所有行的Ordered都被设为同一个数。而上面的两种方法都是先确定每行的排序位置，再精准更新对应行的字段。

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