PostgreSQL嵌套CTE和UNION
|
我正在尝试学习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的组合将会或不会起作用?
这个奥秘解决了:我所观察到的行为是一个已知的错误。我将相同的原始帖子发送到PostgreSQL特定的列表,并得到这个答案:
http://archives.postgresql.org/pgsql-novice/2012-07/msg00113.php (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
