sqlite - 根据递归查询的结果创建表(with 语句)

Question

我想根据 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) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i;

或

WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
CREATE TABLE factorials (n, fct) AS
SELECT * FROM fact_i;

但两者都会导致语法错误。有没有办法在 SQLite 中创建这样的表？

Answer 1

是的，这是可能的(用额外的 SELECT * FROM () 包装):

CREATE TABLE factorials AS
SELECT *
FROM (
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) s;

db<>fiddle demo

编辑:

实际上，您需要做的就是从 CREATE TABLE 中删除列列表:

CREATE TABLE factorials AS
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;

db<>fiddle demo2