PostgreSQL 嵌套 CTE 和 UNION

发布于08月01日

我正在try 学习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和联合的哪些组合将起作用或不起作用？

推荐答案