FROM 中的子查询必须有别名

2021-12-05 00:00:00 postgresql sql subquery oracle

我有一个用 PostgreSQL 编写的查询，它返回一个错误说:

I have this query I have written in PostgreSQL that returns an error saying:

[错误] 错误:
第 3 行:FROM (SELECT DISTINCT (identifiant) AS made_only_recharge

[Err] ERROR:
LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge

这是整个查询:

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'
)

我在 Oracle 中有一个类似的查询，它运行良好.唯一的变化是我在 Oracle 中有 EXCEPT 我用 MINUS 关键字替换了它.我是 Postgres 的新手，不知道它要求什么.处理这个问题的正确方法是什么?

I have a similar query in Oracle that works fine. The only change is where I have EXCEPT in Oracle I have replaced it with the MINUS key word. I am new to Postgres and don't know what it is asking for. What's the correct way of handling this?

推荐答案

在子查询中添加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

相关文章