SQL FROM 中的子查询必须有别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14767209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
subquery in FROM must have an alias
提问by roykasa
I have this query I have written in PostgreSQL that returns an error saying:
我有一个用 PostgreSQL 编写的查询，它返回一个错误说：
[Err] ERROR:
LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge
[Err] 错误：第
3 行：FROM (SELECT DISTINCT (identifiant) AS made_only_recharge
This is the whole query:
这是整个查询：
SELECT COUNT (made_only_recharge) AS made_only_recharge
FROM (
SELECT DISTINCT (identifiant) AS made_only_recharge
FROM cdr_data
WHERE CALLEDNUMBER = '0130'
EXCEPT
SELECT DISTINCT (identifiant) AS made_only_recharge
FROM cdr_data
WHERE CALLEDNUMBER != '0130'
)
I have a similar query in Oracle that works fine. The only change is where I have EXCEPTin Oracle I have replaced it with the MINUSkey word. I am new to Postgres and don't know what it is asking for. What's the correct way of handling this?
我在 Oracle 中有一个类似的查询，可以正常工作。唯一的变化是我EXCEPT在 Oracle 中用MINUS关键字替换了它。我是 Postgres 的新手，不知道它要求什么。处理这个问题的正确方法是什么？
回答by John Woo
add an ALIASon the subquery,
ALIAS在子查询上添加一个，
SELECT COUNT(made_only_recharge) AS made_only_recharge
FROM
(
SELECT DISTINCT (identifiant) AS made_only_recharge
FROM cdr_data
WHERE CALLEDNUMBER = '0130'
EXCEPT
SELECT DISTINCT (identifiant) AS made_only_recharge
FROM cdr_data
WHERE CALLEDNUMBER != '0130'
) AS derivedTable -- <<== HERE
回答by Frank Cheng
In the case of nested tables, some DBMS require to use an alias like MySQL and Oracle but others do not have such a strict requirement, but still allow to add them to substitute the result of the inner query.
在嵌套表的情况下，一些 DBMS 需要使用别名，如 MySQL 和 Oracle，但其他 DBMS 没有这么严格的要求，但仍然允许添加它们来替代内部查询的结果。
