SQL入门求助：如何按字母排序设置SortOrder列的整数值

嗨，刚接触SQL的话，这种批量更新确实容易卡壳，我来帮你搞定这个问题！你已经找对了核心思路——用ROW_NUMBER() OVER (ORDER BY Name ASC)生成按Name排序的序号，现在只需要把这个序号批量更新到原表的SortOrder列里就行。

根据你使用的数据库类型，这里提供几种常用的实现方式：

1. SQL Server 写法 #

用CTE（公共表表达式）来关联原表，这种方式清晰直观，适合SQL Server环境：

WITH UpdatedDesignColours AS (
    SELECT 
        SortOrder,
        ROW_NUMBER() OVER (ORDER BY Name ASC) AS NewSortOrder
    FROM DesignColours
)
UPDATE UpdatedDesignColours
SET SortOrder = NewSortOrder;

原理是先通过CTE计算出每条记录对应的新排序号，然后直接更新CTE中的SortOrder字段——因为CTE和原表是关联的，所以原表的数据会同步更新。

2. MySQL 写法（8.0+版本） #

MySQL 8.0及以上支持窗口函数，你可以用JOIN子查询的方式来实现更新：

-- 如果表有主键（比如ID），优先用主键关联更可靠
UPDATE DesignColours d
JOIN (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY Name ASC) AS NewSortOrder
    FROM DesignColours
) AS temp ON d.ID = temp.ID
SET d.SortOrder = temp.NewSortOrder;

如果表没有主键，也可以用Name关联，但要注意如果存在重复的Name值，会导致更新异常，所以优先用唯一标识列关联：

UPDATE DesignColours d
JOIN (
    SELECT 
        Name,
        ROW_NUMBER() OVER (ORDER BY Name ASC) AS NewSortOrder
    FROM DesignColours
) AS temp ON d.Name = temp.Name
SET d.SortOrder = temp.NewSortOrder;

3. PostgreSQL 写法 #

PostgreSQL可以用CTE结合UPDATE的方式，或者直接JOIN子查询：

-- 方式1：CTE关联更新
WITH UpdatedDesignColours AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY Name ASC) AS NewSortOrder
    FROM DesignColours
)
UPDATE DesignColours
SET SortOrder = UpdatedDesignColours.NewSortOrder
FROM UpdatedDesignColours
WHERE DesignColours.ID = UpdatedDesignColours.ID;

-- 方式2：子查询直接关联
UPDATE DesignColours
SET SortOrder = sub.NewSortOrder
FROM (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY Name ASC) AS NewSortOrder
    FROM DesignColours
) sub
WHERE DesignColours.ID = sub.ID;

重要注意事项 #

• 先备份数据！ 批量更新操作不可逆，尤其是面对大数据集，一定要先在测试环境验证，或者提前备份原表数据。
• 如果你的Name列存在重复值，ROW_NUMBER()会给重复的Name分配不同的序号。如果希望重复Name拥有相同的排序号，可以换成RANK()或DENSE_RANK()函数。
• 始终优先用主键或唯一标识列（比如ID）来关联表，避免因重复字段导致的更新错误。

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