我之前没有使用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,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;
CREATE TABLE statement不允许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;
