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

概述在这个关于加入3个不同表的 pgexercises中,答案如下： select mems.firstname || ' ' || mems.surname as member, facs.name as facility, case when mems.memid = 0 then bks.slots*facs.guestcost 在这个关于加入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 > 30order by cost desc;

发生错误：

06002

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

你问两个问题：
1.

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

2.

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

总结

以上是内存溢出为你收集整理的PostgreSQL不接受WHERE子句中的列别名全部内容，希望文章能够帮你解决PostgreSQL不接受WHERE子句中的列别名所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。