在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)

Answer 2

Tometzky 的回答就足够了，但如果你想要更复杂的东西，你也可以使用 CASE 语句。

Tometzky 使用带有额外指标的 CASE 的示例

SELECT a, CASE WHEN a = any('{1,2,3,5,7}') THEN 'YES' 
  WHEN a = any('{4,9}') THEN 'SQUARE' ELSE 'NO' END as asmallprime
FROM generate_series(1,10) as a;