Case when expression

Case when expression 1. case when expression has two forms

-- Simple Case function CASE sex WHEN '1' then' male 'when' 2' then' female 'else' other 'ends -- Case search function CASEWHEN sex = '1' then' male 'When sex = '2' then' female 'else' others' END

 

2. Use case when in different positions in the statement 2.1 select case when usage

SELECT grade, COUNT (case when sex = 1 THEN 1/* sex 1 for boys, 2 girls */else null end) number of boys, COUNT (case when sex = 2 THEN 1 else null end) Number of girls FROM students group by grade;

 

2.3 where case when usage

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 usage

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, -- alias name 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. For other IF-THEN-ELSE implementations, the 3.1 DECODE () function

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

 

It seems that only Oracle provides this function, and ansi SQL is not supported, and the syntax is not CASE WHEN clear, which is not recommended for personal use. 3.2 Special implementation in the WHERE clause

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