日期:2014-05-16 浏览次数:20623 次
在oracle世界,你可以使用:
1)case表达式 或者
2)decode函数
来实现逻辑判断。Oracle的DECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE是9i以后提供的语法,这个语法更加的灵活,提供了IF THEN ELSE的功能。
case表达式
case表达式,可分两种,简单和搜索,简单case后接表达式,如:
对于简单的case需要几点注意:
1)寻找when的优先级:从上到下
2)再多的when,也只有一个出口,即其中有一个满足了expr就马上退出case
3)不能把return_expr和else_expr指定为null,而且,expr、comparison_expr和return_expr的数据类型必须相同。
搜索case:
CASE WHEN condition THEN return_expr
[WHEN condition THEN return_expr]
...
ELSE else_expr
END
例子:
SELECT (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999' WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999' WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999' WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END) AS BUCKET, COUNT(*) AS Count_in_Group FROM customers WHERE cust_city = 'Marshal' GROUP BY (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999' WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999' WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999' WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000' END); BUCKET COUNT_IN_GROUP ------------- -------------- 0 - 3999 8 4000 - 7999 7 8000 - 11999 7 12000 - 16000 1
用decode可以违反第3NF(行不可再分,列不可再分,列不可重复):列重复
hr@ORCL> select * from a; ID NAME ---------- ---------- 1 a 2 b 3 c 1 a hr@ORCL> select sum(decode(id,1,1,0)) think, 2 sum(decode(id,2,2,0)) water, 3 sum(decode(id,3,3,0)) linshuibin 4 from a; THINK WATER LINSHUIBIN ---------- ---------- ---------- 2 2 3
一个字段,decode函数可以完全改写简单case;
多个字段,需要复杂的case,方可。
语法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返then1,...,如果不等于任何一个if值,则返回else。可以用函数或表达式来替代value,if,then,else从而作出一