postgresql - 在 PostgreSQL 中创建虚拟变量

Question

是否可以在查询时创建一个虚拟变量

例如，下面的查询只会给我满足 var1 条件的观察结果。我还想要剩余的观察结果，但上面有某种标签(0/1，指标值就足够了)

SELECT distinct ON (id) id,var1,var2,var3 
FROM table 
where var2 = ANY('{blue,yellow}');

有

    +-----+------+--------+------+
    | id  | Var1 |  Var2  | Var3 |
    +-----+------+--------+------+
    | 345 |   12 | Blue   | 3456 |
    | 345 |   12 | Red    | 2134 |
    | 346 |   45 | Blue   | 3451 |
    | 347 |   25 | yellow | 1526 |
    +-----+------+--------+------+

想要

+-----+------+--------+------+--------------------+
| id  | Var1 |  Var2  | Var3 | Indicator variable |
+-----+------+--------+------+--------------------+
| 345 |   12 | Blue   | 3456 |                  1 |
| 345 |   12 | Red    | 2134 |                  0 |
| 346 |   45 | Blue   | 3451 |                  1 |
| 347 |   25 | yellow | 1526 |                  1 |
+-----+------+--------+------+--------------------+

Answer 1

您可以在选择输出表达式中使用表达式，而不是 where 中的表达式:

=> select a, a = any('{1,2,3,5,7}') as asmallprime
    from generate_series(1,10) as a;
 a  | asmallprime 
----+-------------
  1 | t
  2 | t
  3 | t
  4 | f
  5 | t
  6 | f
  7 | t
  8 | f
  9 | f
 10 | f
(10 rows)