postgresql - 在 PostgreSQL 的 WHERE 子句中使用函数结果

Question

我试图在 where 子句中使用函数执行的结果但没有成功:

SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE dist<=1;

给我:“dist”列不存在。像这样引用它:

SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE "dist"<=1;

也没有用。请告知 Postgres 是否有可能在 WHERE 子句中使用函数结果而不调用它两次？谢谢!

Answer 1

为了避免调用 distance_between_objects 两次:

--Subquery
SELECT * FROM (
    SELECT 
        *, 
        distance_between_objects(1, id, 7, 3) AS dist 
    FROM 
        clinics) AS clinics_dist 
WHERE 
    dist <= 1;

--CTE
WITH clinics_dist AS (
    SELECT 
        *, 
        distance_between_objects(1, id, 7, 3) AS dist 
    FROM 
        clinics
)
SELECT 
    * 
FROM 
    clinics_dist 
WHERE 
    dist <= 1;

CTE 在我看来是一种更简洁的方法。