将SQLITE查询结果存入临时表

Question

我之前没有使用过SQLite，也无法理解它的语法，如果SQL Server的语法能帮到我的话，我已经在其中实现了这个功能。

我需要将结果放入一个临时表中以便后续重复使用。

//SQL Server

WITH FT_CTE AS
(
SELECT pID, cID FROM brFTNode_Children 
WHERE pID = 1
UNION ALL
    SELECT e.pID, e.cID FROM brFTNode_Children e
    INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)
)
SELECT * INTO #ParentChild FROM FT_CTE;

//SQLite尝试

WITH FT_CTE AS
(
SELECT pID, cID FROM brFTNode_Children 
WHERE pID = 1
UNION ALL
    SELECT e.pID, e.cID FROM brFTNode_Children e
    INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)
)
CREATE TEMPORARY TABLE ParentChild as SELECT * FROM FT_CTE;

我收到了Error near "CREATE": syntax error的错误提示。

- runfastman

Answer 1

CREATE TABLE语句不允许使用CTE，但SELECT语句可以：

CREATE TEMPORARY TABLE ParentChild AS
WITH FT_CTE AS (
    SELECT pID, cID FROM brFTNode_Children 
    WHERE pID = 1
    UNION ALL
    SELECT e.pID, e.cID FROM brFTNode_Children e
    INNER JOIN FT_CTE ftCTE ON (ftCTE.cID = e.pID)
)
SELECT * FROM FT_CTE;

- CL.