oracle case when then

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

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;

 

2.3 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.4 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的其他实现

3.1 DECODE() 函数

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from   employees;

貌似只有Oracle提供该函数，而且不支持ANSI SQL，语法上也没CASE WHEN清晰，个人不推荐使用。

3.2 在WHERE中特殊实现

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%')

这种方法也是在特殊情况下使用，要多注意逻辑，不要弄错。

--@
insert into sd_claiml(
   ClaimNo,
   GroupClaimNo,
   ClaimType,
   ClaimDate,
   LossDate,
   LossCause,
   LossArea,
   Name,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo,
   LossResult,
   LossResultDate,
   IsCancelClaim,
   CancelDate,
   makedate
)
select rg.rgtno,
       '',
       '02',
       to_char(rg.rgtdate, 'yyyymmddhh'),
       to_char(rg.accidentdate, 'yyyymmddhh'),
       '01',
       '',
       lp.name,
       lp.sex,
       to_char(lp.birthday, 'yyyymmdd'),
       (case trim(lp.idtype)
         when '0' then
          '01'
         when '1' then
          '07'
         when '2' then
          '04'
         when '3' then
          '03'
         when '5' then
          '02'
         when '6' then
          '09'
         else
          '99'
       end),
       lp.idno,
       (case (select SUBSTR(lar.reasoncode, 2, 4)
            from llappclaimreason lar
           where lar.caseno = rg.rgtno
             and rownum = 1)
         when '01' then
          '03'
         when '03' then
          '02'
         when '02' then
          '01'
         else
          '99'
       end),
       to_char(rg.accidentdate, 'yyyymmddhh'),
       '0',
       '',
       sysdate
  from llregister rg, ldperson lp, llcase lc
 where rg.rgtno = lc.caseno
   and lc.customerno = lp.customerno
   and rg.mngcom like '8606%'
  -- and rg.makedate = trunc(sysdate-1)
   and rg.makedate = trunc(sysdate)
   and substr(rg.maketime,0,2)=to_char(sysdate-1/24,'hh24')