PostgreSQL:使用别名列时的情况

Question

我想做这样的事情:

select 
case when (select count(*) as score from users t1 )   >5   THEN score   else 0 end 

当我尝试时出现错误:

column score doesn't exists. 

我可以用其他方式做到这一点吗？我需要它来设置一个 LIMIT 值。我当然想这样做:

select 
case when (select count(*) as score from users t1 )   >5   THEN (select count(*) as score from users)    else 0 end 

但我需要执行两次相同的查询。有人有想法吗？

Answer 1

您可以使用 WITH 子句:

with a as (select count(*) score from t)
select case when score > 5 then score else 0 end from a;

或子查询(内联 View ):

select case when score > 5 then score else 0 end 
from (select count(*) score from t) t;