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

发布时间：2020-12-13 16:30:40 所属栏目：百科来源：网络整理

导读：在这个关于加入3个不同表的 pgexercises中,答案如下： 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.membe

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

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列表中引用成本别名？
如果我运行相同的查询：

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

发生错误：

06002

从this answer开始,我很清楚,这是因为评估的顺序.但为什么要按成本排序;被允许？

你问两个问题：
1.

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

But why order by cost desc; is allowed?

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.

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

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

有关：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!