Oralce Case When的用法-优快云博客

本文通过多个实际案例展示了 SQL 中 Case 函数的应用场景和技术细节，包括 Case 当表达式用于条件判断、统计不同性别数量、结合 Group By 进行薪资级别划分等高级用法。


--简单Case函数  

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END  

--Case搜索函数  

CASE
WHEN sex = '1' THEN '男'  
WHEN sex = '2' THEN '女'  
ELSE '其他' END

--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;


--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


--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;


---统计 count 和 distinct 的使用
select t1.*,t2.* from (select 2013-09 vtime,
		count(case when adid=9058 then id else null end) ytznum,
		count(case when adid=9059 then id else null end) jtznum,
		count(distinct(case when adid=9058 then ip else null end)) yipnum,
		count(distinct(case when adid=9059 then ip else null end)) jipnum
		from v_user_store where createtime >= to_date('2013-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and createtime <=  to_date('2013-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') 
		) t1 ,
		(select 2013-09 otime,
		count(case when adid=9058 then 1 else null end) yordernum,
		count(case when adid=9059 then 1 else null end) jordernum,
		sum(case when adid=9058 then costs else 0 end) yordercost,
		sum(case when adid=9059 then costs else 0 end) jordercost,
		decode(count(case when adid=9058 then 1 else null end),0,0,round((sum(case when adid=9058 then costs else 0 end) / count(case when adid=9058 then 1 else null end)),2)) yavgcost,
		decode(count(case when adid=9059 then 1 else null end),0,0,round((sum(case when adid=9059 then costs else 0 end) / count(case when adid=9059 then 1 else null end)),2)) javgcost
		from v_order2
		where status <> 2 and ordertime >=  to_date('2013-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and ordertime <=  to_date('2013-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') ) t2  order by t2.otime