如何在 PostgreSQL 的 WHERE 子句中使用我计算参数的别名(在 SELECT 子句中)以防止重复?

问题描述

在下面的查询中，参数 date_part('year', CURRENT_DATE) - f.birth_year 重复了 3 次.我怎样才能用它的别名 age 替换它?

In the query below, the parameter date_part('year', CURRENT_DATE) - f.birth_year is repeated three times. How can I replace it by its alias age ?

SELECT
    date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM
    public.foo f
WHERE
    date_part('year', CURRENT_DATE) - f.birth_year >=20 AND
    date_part('year', CURRENT_DATE) - f.birth_year <=30

推荐答案

您不能在 where 子句中重复使用 select 别名.您需要重复表达式，或者使用子查询或 cte.

You can't reuse a select alias in the where clause. You need to repeat the expression, or use a subquery or cte.

就其价值而言，您可以使用 between 使表达式在 where 子句中只出现一次而不是两次:

For what it's worth, you can use between to have the expression just once instead of twice in the where clause:

SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30

至于子查询解决方案，那就是:

As for the subquery solution, that would be:

SELECT *
FROM (
    SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
    FROM public.foo
) f
WHERE age BETWEEN 20 AND 30

这篇关于如何在 PostgreSQL 的 WHERE 子句中使用我计算参数的别名(在 SELECT 子句中)以防止重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！