问PostgreSQL：如何使用嵌套CTE和UNION？

提问于 2018-03-21 04:50:47

回答 1关注 0查看 167

我正在尝试学习SQL，使用PostgreSQL 9.1.3。即：

这样做是可行的：

WITH innermost AS (SELECT 2)
SELECT * FROM innermost
UNION SELECT 3;

结果：

 ?column? 
----------
        2
        3

这样做是可行的：

WITH outmost AS (
        (WITH innermost AS (SELECT 2)
         SELECT * FROM innermost)
)                                
SELECT * FROM outmost;

结果：

?column? 
----------
        2

这也适用于：

WITH outmost AS (
  SELECT 1
  UNION (WITH innermost AS (SELECT 2)
         SELECT * FROM innermost)
)
SELECT * FROM outmost;

结果：

 ?column? 
----------
        1
        2

但这确实不工作：

WITH outmost AS (
  SELECT 1
  UNION (WITH innermost as (SELECT 2)
         SELECT * FROM innermost
         UNION SELECT 3)
)
SELECT * FROM outmost;

结果：

ERROR:  relation "innermost" does not exist
LINE 4:          SELECT * FROM innermost

是否有一些一般规则可以让我预测嵌套的CTE和UNION的哪些组合将起作用或不起作用？

相似问题