PostgreSQL不接受WHERE子句中的列别名

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

关于加入3个不同的表的这个方法,答案如下:

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;

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子句的SELECT列表中引用成本别名?如果我运行相同的查询:

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

an error occurs:

发生错误:

ERROR: column "cost" does not exist

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 个解决方案

#1

You ask two questions:
1.

你问两个问题:1。

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

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

2.

But why order by cost desc; is allowed?

但为什么要按成本排序;被允许?

The manual has an answer for both of them here:

手册中有两个答案:

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.

输出列的名称可用于在ORDER BY和GROUP BY子句中引用列的值,但不能在WHERE或HAVING子句中引用;你必须写出表达式。

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.

它由SQL标准定义,原因是SELECT查询中的事件序列。在应用WHERE子句时,尚未计算SELECT列表中的输出列。但是当谈到ORDER BY时,输出列很容易获得。

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

因此,虽然这一开始不方便且令人困惑,但它仍然有意义。

Related:

• PostgreSQL Where count condition

PostgreSQL在哪里计算条件

• Best way to get result count before LIMIT was applied

在应用LIMIT之前获得结果计数的最佳方法