要了解递归的行为(在SQLite中),我尝试使用以下语句用递归语句对表的行重新编号:
我们创建一个示例表,
CREATE TABLE tb
(x TEXT(1) PRIMARY KEY);
INSERT INTO tb
VALUES ('a'),('b'),('c');
并重新编号从2开始的行,
SELECT tb.x as x,tb.rowid + 1 as idx from tb;
/* yields expected:
a|2
b|3
c|4
*/
尝试对递归WITH
(忽略ROWID
)执行相同操作会导致分歧-在这里,我添加了LIMIT 6
以防止分歧:
WITH RECURSIVE
newtb AS (
SELECT tb.x,2 AS idx FROM tb
UNION ALL
SELECT tb.x,newtb.idx + 1
FROM tb,newtb
LIMIT 6 -- only to prevent divergence!
)
SELECT * FROM newtb;
/* yields indefinitely:
a|2
b|2
c|2
a|3
b|3
c|3
...
*/
为什么递归到达表tb
的末尾时并没有停止?可以预防吗?
请注意,可以将问题重新表述为如何在SQLite中产生以下过程伪代码的结果(事不宜迟):
tb := {'a','b','c'};
num := {1,2,3};
result := {}; # initialize an empty table
for i in {1,...,length(tb)} # assume index starts from 1
append tuple(num[i],tb[i]) to result;
end for
# result will be {(1,'a'),(2,'b'),(3,'c')}
这等效于Python之类的zip
操作。
根据@CPerkins的提示,可以非常优雅地通过window functions(对于SQLite> = 3.25)实现此目标;例如,
SELECT (row_number() OVER (ORDER BY x)) + 2 AS newId,x FROM tb;