case when 有简单case函数和case搜索函数两种写法:
–简单case函数
CASE c1
WHEN ‘1’ THEN ‘A’
WHEN ‘2’ THEN ‘B’
ELSE ‘C’ END
–case搜索函数
CASE WHEN c1 = ‘1’ THEN ‘A’
WHEN c1 = ‘2’ THEN ‘B’
ELSE ‘C’ END
使用例子:
SQL> select * from test;
行号 a b
---------- ----------- -----------
1 1 1
2 1 2
3 2 1
4 2 2
5 NULL NULL
如果a为空,则返回‘null’,如果a=b则返回‘true’,否则返回’false’
SQL> select case when a is null then 'null' when a = b then 'true' else 'false' end from test ;
行号 CASEWHENaISNULLTHEN'null'WHENa=bTHEN'true'ELSE'false'END
---------- --------------------------------------------------------
1 true
2 false
3 false
4 true
5 null