postgresql - 创建 2 个依赖于 postgresql 的新条件列

Question

我想从 postgresql 中的查询创建两个新列，一个取决于现有数据，另一个取决于新列，即

existing_col   new_col   new_col2
a              1         2
b              0         0

我试过:

select existing_col, 
case when existing_col like 'a' then 1 else 0 end  as new_col  
case when new_col like 1 then 2 else 0 end as new_col2
from table

然而，这给我的错误是 new_col 不存在，我该如何实现呢？

Answer 1

更新:

(我修改了你的 qry 一点不避免整数运算符)

t=# create table "table" (existing_col text);
CREATE TABLE
Time: 50.189 ms
t=# insert into "table" values('a'),('b');
INSERT 0 2
Time: 0.911 ms
t=# select *,case when new_col like 1 then 2 else 0 end as new_col2
t-#     from (
t(#       select existing_col,
t(#       case when existing_col like 'a' then 1 else 0 end as new_col
t(#     from "table") al
t-# ;
ERROR:  operator does not exist: integer ~~ integer
LINE 1: select *,case when new_col like 1 then 2 else 0 end as new_c...
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Time: 0.514 ms
t=#     select *,case when new_col = 1 then 2 else 0 end as new_col2
t-#     from (
t(#       select existing_col,
t(#       case when existing_col like 'a' then 1 else 0 end as new_col
t(#     from "table") al
t-# ;
 existing_col | new_col | new_col2
--------------+---------+----------
 a            |       1 |        2
 b            |       0 |        0
(2 rows)

Time: 0.347 ms

as in docs:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END