Oracle Case When usage introduction

1. There are two forms of case-when expression

--simple case function case sex when'1'Then'male' when'2'Then'female'ELSE'other'END--Case search function Casewhen sex='1'Then'male'When sex='2'Then'female'ELSE'other'END

2. When a case is used in a different position in a statement

2.1 SELECT Case

SELECT   11/      *sex 1 for boys, 2 girls */                       ELSE NULL                       END) Number of boys                ,21                       ELSE NULL                       END) Number of girls from    students GROUP by grade;

2.2 WHERE Case when usage

SELECT Case when salary<= -Then'1'When salary> -and Salary <= -Then'2'When salary> -and Salary <= -Then'3'When salary> -and Salary <= +Then'4'ELSE NULL END salary_class,--Alias name Count (*) from Table_a GROUP by case when salary<= -Then'1'When salary> -and Salary <= -Then'2'When salary> -and Salary <= -Then'3'When salary> -and Salary <= +Then'4'ELSE NULL END;

2.3 Other implementations of If-then-else

SELECT t2.*, t1.*From T1, T2 WHERE ( case when T2.compare_type='A'and T1. Some_type like'nothing%' Then1When T2.compare_type!='A'and T1. Some_type not like'nothing%' Then1ELSE0END)=1

3. GROUNP by case when usage
3.1 DECODE () function

Select ' M ' ' Male ' ' F ' ' Female ' ' Unknown ')  from   Employees

It seems that only Oracle provides this function, and does not support ANSI SQL, there is no syntax when the case is clear, not recommended for personal use.

3.2 Special implementations in where

SELECTT2.*, T1.*    fromT1, T2WHERE(T2.compare_type= 'A'  andT1. Some_type like 'nothing%')OR(T2.compare_type!= 'A'  andT1. Some_type not  like 'nothing%')

#这种方法也是在特殊情况下使用, pay more attention to logic, don't get it wrong

Oracle Case When usage introduction