Postgreql 中的 IF-THEN-ELSE 语句

我希望写一个后期查询，以执行以下操作:

if(field1 > 0,  field2 / field1 , 0)

我试过这个查询，但是没有用

if (field1 > 0)
then return field2 / field1 as field3
else return 0 as field3

谢谢

285047 次浏览
小开 
case when field1>0 then field2/field1 else 0 end as field3
小开
最佳答案

As stated in PostgreSQL docs 给你:

SQL CASE 表达式是一般条件表达式，类似于其他编程语言中的 if/else 语句。

代码片段特别回答了您的问题:

SELECT field1, field2,
CASE
WHEN field1>0 THEN field2/field1
ELSE 0
END
AS field3
FROM test
小开

一般来说，case when ...的替代品是 coalesce(nullif(x,bad_value),y)(在 OP 中不能使用),

select coalesce(nullif(y,''),x), coalesce(nullif(x,''),y), *
from (     (select 'abc' as x, '' as y)
union all (select 'def' as x, 'ghi' as y)
union all (select '' as x, 'jkl' as y)
union all (select null as x, 'mno' as y)
union all (select 'pqr' as x, null as y)
) q

提供:

 coalesce | coalesce |  x  |  y
----------+----------+-----+-----
abc      | abc      | abc |
ghi      | def      | def | ghi
jkl      | jkl      |     | jkl
mno      | mno      |     | mno
pqr      | pqr      | pqr |
(5 rows)

提交答案