Usage of the oracle case when statement

1. The case when expression has two forms:
Copy codeThe Code is as follows: -- simple Case Function
CASE sex
WHEN '1' THEN 'male'
WHEN '2' THEN 'female'
ELSE 'others' END
-- Case search function
CASE
WHEN sex = '1' THEN 'male'
WHEN sex = '2' THEN 'female'
ELSE 'others' END

2. Usage of case when in different positions in the statement
2.1 select case when usage
Copy codeThe Code is as follows: SELECT grade, COUNT (case when sex = 1 THEN 1/* sex 1 for boys, 2 girls */
ELSE NULL
END) boys,
COUNT (case when sex = 2 THEN 1
ELSE NULL
END) Number of girls
FROM students group by grade;

2.2 where case when usage
Copy codeThe Code is as follows: 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 usage
Copy codeThe Code is as follows: 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
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. Other IF-THEN-ELSE implementations
3.1 DECODE () function
Copy codeThe Code is as follows: 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
Copy codeThe Code is as follows: 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 % ')

This method is also used in special cases. pay more attention to the logic and avoid mistakes.