在PostgreSQL中使用REC里面的WITH [复制]
发布时间:2020-12-13 16:01:36 所属栏目:百科 来源:网络整理
导读:参见英文答案 Issue with recursive CTE in PostgreSQL????????????????????????????????????1个 ???????????? Multiple CTE in single query????????????????????????????????????2个 在PostgreSQL中,另一个WITH可以使用WITH,例如: WITH num AS ( VALUES (
参见英文答案 >
Issue with recursive CTE in PostgreSQL????????????????????????????????????1个
>???????????? Multiple CTE in single query????????????????????????????????????2个 在PostgreSQL中,另一个WITH可以使用WITH,例如: WITH num AS ( VALUES (50) ),num2 AS ( SELECT column1 * 2 AS value FROM num ) SELECT value FROM num2; 还有RECURSIVE WITHs,可以这样做: WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; 但到目前为止,我还没有找到一种方法让RECURSIVE WITH使用之前的WITH.我认为它应该是这样的: WITH num AS ( VALUES (50) ),num2 AS ( SELECT column1 * 2 AS value FROM num ),RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < (SELECT * FROM num2) ) SELECT sum(n) FROM t; 但这不起作用.有没有办法做到这一点?如果是这样,怎么样? 解决方法
从WITH RECURSIVE开始.您仍然可以在递归部分之前挤入“常规”CTE:
WITH RECURSIVE num AS (VALUES (50)),num2 AS (SELECT column1 * 2 AS value FROM num),t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < (SELECT * FROM num2) ) SELECT sum(n) FROM t; -> SQLfiddle demo. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |