Oracle CASE WHEN 用法介绍

Oracle CASE WHEN 用法介绍

1. CASE WHEN 表达式有两种形式

    	--简单Case函数  
   
    		CASE sex  
    		WHEN '1' THEN '男'  
    		WHEN '2' THEN '女'  
    		ELSE '其他' END  
   
    	--Case搜索函数  
   
    		CASE
    		WHEN sex = '1' THEN '男'  
    		WHEN sex = '2' THEN '女'  
    		ELSE '其他' END  
   

2. CASE WHEN 在语句中不同位置的用法

    2.1 		SELECT CASE WHEN 用法
    				//sex 1为男生，2为女生                      		
    			          SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1    
                          ELSE NULL
                          END) 男生数,
                          COUNT (CASE WHEN sex = 2 THEN 1
                          ELSE NULL
                          END) 女生数
                          FROM students GROUP BY grade;
   2.2         WHERE CASE WHEN 用法
                  SELECT T2.*, T1.*
    				FROM T1, T2
    				WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
               			T1.SOME_TYPE LIKE 'NOTHING%'
            			THEN 1
          			WHEN T2.COMPARE_TYPE != 'A' AND
               			T1.SOME_TYPE NOT LIKE 'NOTHING%'
            			THEN 1
         			 ELSE 0
       			END) = 1
   2.3        GROUP BY CASE WHEN 用法
    					SELECT  
    					CASE WHEN salary <= 500 THEN '1'  
    					WHEN salary > 500 AND salary <= 600  THEN '2'  
    					WHEN salary > 600 AND salary <= 800  THEN '3'  
    					WHEN salary > 800 AND salary <= 1000 THEN '4'  
    					ELSE NULL END salary_class, -- 别名命名
    					COUNT(*)  
    					FROM    Table_A  
    					GROUP BY  
    					CASE WHEN salary <= 500 THEN '1'  
    					WHEN salary > 500 AND salary <= 600  THEN '2'  
    					WHEN salary > 600 AND salary <= 800  THEN '3'  
    					WHEN salary > 800 AND salary <= 1000 THEN '4'  
    					ELSE NULL END;
   

   3.关于IF-THEN-ELSE的其他实现

    		SELECT T2.*, T1.*
    		FROM T1, T2
    		WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%')
     			OR
    		(T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')