PostgreSQL CASE ... END 有多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27800119/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL CASE ... END with multiple conditions
提问by wiltomap
Here is an extract of my table:
这是我的表的摘录：
gid | datepose | pvc
---------+----------------+------------
1 | 1961 | 01
2 | 1949 |
3 | 1990 | 02
1 | 1981 |
1 | | 03
1 | |
I want to fill the PVC column using a SELECT CASEas bellow:
我想使用SELECT CASE如下填充 PVC 列：
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 ;
The result is the same content as source table, nothing has happened and I get no error message in pg_log files. It might be a syntax error, or a problem with using multiple conditions within WHEN clauses?
结果与源表的内容相同，什么也没发生，我在 pg_log 文件中没有收到错误消息。这可能是语法错误，还是在 WHEN 子句中使用多个条件的问题？
Thanks for help and advice!
感谢您的帮助和建议！
回答by Soni Harriz
This kind of code perhaps should work for You
这种代码可能适合你
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)
