Rexdf

The devil is in the Details.

ORACLE CASE WHEN

| Comments

ORACLE CASE WHEN 及 SELECT CASE WHEN的用法

分类: ORACLE SQL优化2009-08-12 14:10 6644人阅读 评论(0) 收藏 举报

CASE 语句

CASE selector WHEN value1 THEN action1; WHEN value2 THEN action2; WHEN value3 THEN action3; ….. ELSE actionN; END CASE;

CASE表达式

DECLARE temp VARCHAR2(10); v_num number; BEGIN v_num := &i; temp := CASE v_num WHEN 0 THEN ‘Zero’ WHEN 1 THEN ‘One’ WHEN 2 THEN ‘Two’ ELSE NULL END; dbms_output.put_line(‘v_num = ‘ temp); END; /

CASE搜索语句

CASE WHEN (boolean_condition1) THEN action1; WHEN (boolean_condition2) THEN action2; WHEN (boolean_condition3) THEN action3; …… ELSE    actionN; END CASE;

CASE搜索表达式

DECLARE a number := 20; b number := -40; tmp varchar2(50); BEGIN tmp := CASE WHEN (a>b) THEN ‘A is greater than B’ WHEN (a<b) THEN ‘A is less than B’ ELSE ‘A is equal to B’ END; dbms_output.put_line(tmp); END; /

SELECT CASE WHEN 的用法

select 与 case结合使用最大的好处有两点,一是在显示查询结果时可以灵活的组织格式,二是有效避免了多次对同一个表或几个表的访问。下面举个简单的例子来说明。例如表 students(id, name ,birthday, sex, grade),要求按每个年级统计男生和女生的数量各是多少,统计结果的表头为,年级,男生数量,女生数量。如果不用select case when,为了将男女数量并列显示,统计起来非常麻烦,先确定年级信息,再根据年级取男生数和女生数,而且很容易出错。用select case when写法如下: SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /sex 1为男生,2位女生/ ELSE NULL END) 男生数, COUNT (CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数 FROM students GROUP BY grade;

Comments