在这个关于加入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.
原文链接：https://www.f2er.com/postgresql/192813.html1.
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 BYandGROUP BYclauses,but not in theWHEREorHAVING
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
