SQL> select dummy from dual;
D
-
X
SQL>
看下面的case when语句:
select case
when dummy = 'X' then'1'
when dummy <> 'Y' then
'2'
else
'0'
end
from dual;
该返回什么?
SQL> select case
2 when dummy = 'X' then
3 '1'
4 when dummy <> 'Y' then
5 '2'
6 else
7 '0'
8 end
9 from dual;
C
-
1
SQL>
当第一个when子句判断成功时,整个case逻辑判断就返回数据并立即结束。c语言也是这样的吗?搞忘了。
在group by子句中也可以有效地嵌入case when 的逻辑判断,这可以使得代码更加精炼:
SQL> select (case
2 when sal <= 1000 then
3 '低档'
4 when sal <= 2000 then
5 '较低档'
6 when sal <= 3000 then
7 '中档'
8 when sal <= 4000 then
9 '高档'
10 else
11 '较高档'
12 end) as 档次, count(*)
13 from emp
14 group by (case
15 when sal <= 1000 then
16 '低档'
17 when sal <= 2000 then
18 '较低档'
19 when sal <= 3000 then
20 '中档'
21 when sal <= 4000 then
22 '高档'
23 else
24 '较高档'
25 end)
26 ;
档次 COUNT(*)
------ ----------
较高档 1
中档 5
较低档 7
低档 1
SQL>
这种用法,在报表sql中比较常见。