sub select时PostgreSQL列不存在

Question

我有一个名为user的表，我需要让另一个用户在特定的距离范围内，以及他们之间的距离。我的问题是：

select ( 6371 * acos( 
                cos( radians(users.latitude) )
              *    cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) as distance, users from users where users.id != 41 and distance > 50

我需要恢复用户列表和距离，但"as distance“不起作用：

ERROR:  column "distance" does not exist. 

我尝试过：

with distance as (
select ( 6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) from users
)
select * from users where users.id != 41 and distance > 50

但是，错误保持不变。

Answer 1

在Postgres中，不能在where子句中使用列别名。这就是你的第一个查询不能工作的原因。

可以使用CTE进行第二个查询，但是distance是一个虚拟表，而不是一个列。加入它吧。

with users_distance as (
  select
    id,
    ( 6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              ) 
    ) as distance
  from users
)
select *
from users
join users_distance ud on users.id = ud.id
where users.id != 41 and ud.distance > 50

如果您经常这样做，请考虑向表中添加一个generated column，这样您就不必一直重新计算它。

alter table users add column distance numeric
  generated always as (
    6371 * acos( 
                cos( radians(users.latitude) )
              * cos( radians(-22.9035) )
              * cos( radians(-43.2096) - radians(users.longitude) )
              + sin( radians(users.latitude) )
              * sin( radians(-22.9035) ) 
              )
  ) stored

Try it

最后，与其自己进行这些计算，不如考虑使用功能非常强大的PostGIS扩展。