如何在SQLite中为排序后的表生成行序并更新至指定列

针对你的需求，可以通过窗口函数结合UPDATE语句实现，具体步骤如下：

实现SQL语句 #

WITH ranked AS (
  SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS new_sortId
  FROM t0
)
UPDATE t0
SET sortId = ranked.new_sortId
FROM ranked
WHERE t0.name = ranked.name;

语句说明 #

1. 生成排序序号：使用ROW_NUMBER() OVER (ORDER BY name)窗口函数，按name列的升序（A→B→C）为每行生成从1开始的连续序号，这个序号就是需要存入sortId的值。
2. 临时结果集：通过WITH子句创建临时表ranked，存储每个name对应的目标排序号。
3. 更新原表：通过name字段关联原表t0和临时表ranked，将原表的sortId列更新为对应的序号。

验证结果 #

执行更新后，查询表数据：

SELECT name, sortId FROM t0 ORDER BY name;

将得到符合预期的结果：

name sortId
---- ------
A    1
B    2
C    3

注：如果name列存在重复值，若希望相同name对应相同序号，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()，根据实际需求选择。

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