具有多个条件的 PostgreSQL CASE... END

下面是我的表格的摘录:

  gid    |    datepose    |    pvc
---------+----------------+------------
1       |  1961          | 01
2       |  1949          |
3       |  1990          | 02
1       |  1981          |
1       |                | 03
1       |                |

我想填充 PVC 柱使用 SELECT CASE如下:

SELECT


gid,


CASE
WHEN (pvc IS NULL OR pvc = '') AND datpose < 1980) THEN '01'
WHEN (pvc IS NULL OR pvc = '') AND datpose >= 1980) THEN '02'
WHEN (pvc IS NULL OR pvc = '') AND (datpose IS NULL OR datpose = 0) THEN '03'
END AS pvc


FROM my_table ;

结果是与源表相同的内容，什么都没有发生，并且在 pg _ log 文件中没有得到错误消息。这可能是语法错误，或者在 WHERE 子句中使用多个条件的问题？

谢谢你的帮助和建议！

384731

小开

最佳答案

这种类型的代码也许应该为您工作

SELECT
*,
CASE
WHEN (pvc IS NULL OR pvc = '') AND (datepose < 1980) THEN '01'
WHEN (pvc IS NULL OR pvc = '') AND (datepose >= 1980) THEN '02'
WHEN (pvc IS NULL OR pvc = '') AND (datepose IS NULL OR datepose = 0) THEN '03'
ELSE '00'
END AS modifiedpvc
FROM my_table;




gid | datepose | pvc | modifiedpvc
-----+----------+-----+-------------
1 |     1961 | 01  | 00
2 |     1949 |     | 01
3 |     1990 | 02  | 00
1 |     1981 |     | 02
1 |          | 03  | 00
1 |          |     | 03
(6 rows)