PostgreSQL在WHERE子句中不接受列别名 [英] PostgreSQL does not accept column alias in WHERE clause

问题描述

在有关连接3个不同表的 pgexerc 中，答案如下:

In this pgexercises about joining 3 different tables, the answer is given as following:

select mems.firstname || ' ' || mems.surname as member, 
    facs.name as facility, 
    case 
        when mems.memid = 0 then
            bks.slots*facs.guestcost
        else
            bks.slots*facs.membercost
    end as cost
        from
                cd.members mems                
                inner join cd.bookings bks
                        on mems.memid = bks.memid
                inner join cd.facilities facs
                        on bks.facid = facs.facid
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and (
            (mems.memid = 0 and bks.slots*facs.guestcost > 30) or
            (mems.memid != 0 and bks.slots*facs.membercost > 30)
        )
order by cost desc;

为什么不能在WHERE子句的SELECT列表中引用cost别名?
如果我使用以下命令运行相同的查询:

Why can't I refer to the cost alias in the SELECT list in the WHERE clause?
If I run the same query with:

        ...
        where
        bks.starttime >= '2012-09-14' and 
        bks.starttime < '2012-09-15' and
        cost > 30
order by cost desc;

发生错误:

ERROR: column "cost" does not exist

从此答案中我可以清楚地了解到评价.但是为什么允许order by cost desc;?

It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc; is allowed?

推荐答案

您问两个问题:
1.

You ask two questions:
1.

为什么我不能在WHERE子句中引用SELECT成本别名?

Why can't I refer to the SELECT cost alias at the WHERE clause?

2.

但是为什么要按成本顺序订购；被允许吗?

But why order by cost desc; is allowed?

手册对以下两个方面都有答案他们在这里:

输出列的名称可以用来引用列中的值 ORDER BY和GROUP BY子句，但不在WHERE或HAVING中 条款；在那里，您必须写出表达式.

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

它是由 SQL标准定义的，原因是SELECT查询中的事件顺序.在应用WHERE子句时，尚未计算SELECT列表中的输出列.但是，当涉及到ORDER BY时，输出列就可以使用了.

It's defined by the SQL standard and the reason is the sequence of events in a SELECT query. At the time WHERE clauses are applied, output columns in the SELECT list have not yet been computed. But when it comes to ORDER BY, output columns are readily available.

因此，尽管起初这不方便且令人困惑，但还是有道理的.

So while this is inconvenient and confusing at first, it still kind of makes sense.

相关:

• PostgreSQL的位置计数条件
• 获得结果的最佳方法应用LIMIT之前的计数

• PostgreSQL Where count condition
• Best way to get result count before LIMIT was applied

这篇关于PostgreSQL在WHERE子句中不接受列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！