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

问题描述

在这个pgexercises中关于加入3个不同的表，答案如下:

select mems.firstname ||' ' ||mems.surname 作为成员，facs.name 作为设施，案件当 mems.memid = 0 时bks.slots*facs.guestcost别的bks.slots*facs.membercost作为成本结束从cd.members mems内连接 cd.bookings bks在 mems.memid = bks.memid内连接 cd.facilities facs在 bks.facid = facs.facid在哪里bks.starttime >='2012-09-14' 和bks.starttime <'2012-09-15' 和 ((mems.memid = 0 and bks.slots*facs.guestcost > 30) 或(mems.memid != 0 and bks.slots*facs.membercost > 30))按成本降序排序；

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

<代码> ...在哪里bks.starttime >='2012-09-14' 和bks.starttime <'2012-09-15' 和成本 >30按成本降序排序；

出现错误:

<块引用>

错误:成本"列不存在

我从 这个答案 很清楚这是因为顺序的评价.但是为什么允许order by cost desc;?

解决方案

你问两个问题:
1.

<块引用>

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

2.

<块引用>

但为什么按成本降序排序；允许吗?

手册对两者都有答案他们在这里:

<块引用>

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

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

因此，虽然一开始这很不方便且令人困惑，但它仍然是有道理的.

相关:

• PostgreSQL Where计数条件
• 获得结果的最佳方式在应用 LIMIT 之前计数

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;

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;

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?

解决方案

You ask two questions:
1.

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

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.

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.

Related:

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

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