sql - 为什么 postgresql case 语句的结果在 where 子句中不起作用

Question

我正在解决这个站点中的 PostgreSQL 问题。
我的回答是：

select concat(mem.firstname, ' ', mem.surname), fac.name as facility,
case when mem.memid=0 then bks.slots*fac.guestcost
     else bks.slots*membercost
end  as cost
from cd.members mem
inner join cd.bookings bks on mem.memid=bks.memid
inner join cd.facilities fac on bks.facid=fac.facid

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

但它显示ERROR: column "cost" does not exist了cost > 30insideWHERE子句的错误。如果在cost里面工作ORDER BY，那为什么它在里面不工作WHERE？

标签： sqlpostgresqlcasewhere-clausecolumn-alias

Answer 1

这是对的。您不能使用在 中定义的列SELECT别名WHERE。简单的解决方案是使用横向连接来定义列别名：

select concat(mem.firstname, ' ', mem.surname), fac.name as facility,
       v.cost
from cd.members mem join
     cd.bookings bks
     on mem.memid = bks.memid join
     cd.facilities fac
     on bks.facid=fac.facid cross join
     (values (case when mem.memid = 0 then bks.slots * fac.guestcost
                   else bks.slots * membercost
              end)
     ) v(cost)
where bks.starttime >= '2012-09-14' and bks.starttime < '2012-09-15' and
      v.cost > 30
order by v.cost desc;