根据递归查询的结果(使用语句)创建一个表

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;

编辑：

实际上，您需要做的就是从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;