我想根据SQLite中的递归查询结果创建一个新的(持久)表。
例如,让我们创建一个包含列(n,fct)
的表,其中n
代表自然数,fct
代表阶乘:
-- factorial of n = 0,1,...,5
WITH RECURSIVE fact_i (n,fct) AS (
VALUES (0,1)
UNION ALL
SELECT n+1,fct * (n+1) FROM fact_i
WHERE n < 5)
SELECT * FROM fact_i;
结果
0|1
1|1
2|2
3|6
4|24
5|120
但是,我想将结果存储在一个持久表中,例如factorials
。
我尝试过
CREATE TABLE factorials (n,fct) AS
WITH RECURSIVE fact_i (n,fct * (n+1) FROM fact_i
WHERE n < 5)
SELECT * FROM fact_i;
或
WITH RECURSIVE fact_i (n,fct * (n+1) FROM fact_i
WHERE n < 5)
CREATE TABLE factorials (n,fct) AS
SELECT * FROM fact_i;
但是两者都导致语法错误。 有没有办法在SQLite中创建这样的表?