如何在SQLite中获取排序表的ROWID或行顺序？

kzmpq1sx 于 2023-03-08 发布在 SQLite

关注(0)|答案(1)|浏览(293)

我需要按一个属性对一个给定的表进行排序，然后将排序后的表的顺序存储在SQLite中该表的一列中。
样表：

CREATE TABLE t0 (name TEXT, sortId INT)
INSERT INTO t0 VALUES ('C', NULL)
INSERT INTO t0 VALUES ('A', NULL)
INSERT INTO t0 VALUES ('B', NULL)

现在我需要按属性"name"对表进行排序，并通过UPDATE命令将排序后的行的索引存储在列"sortId"中。

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

如何才能做到这一点？

1条答案

在CTE中使用ROW_NUMBER()窗口函数获取每行的排名，并在UPDATE语句中将其连接到表中：

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

请参见demo。
对于不支持UPDATE...FROM语法甚至窗口函数的较早版本的SQLite，您可以使用相关子查询：

UPDATE t0
SET sortId = (SELECT COUNT(*) + 1 FROM t0 AS t WHERE t.name < t0.name);

参见demo。