我正在尝试学习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的组合是否有效?