1. The case-when expression has two forms
Copy Code code as follows:
--Simple Case function
Case Sex
When ' 1 ' THEN ' male '
When ' 2 ' THEN ' woman '
Else ' other ' end
--case search function
Case
When sex = ' 1 ' THEN ' male '
When sex = ' 2 ' THEN ' female '
Else ' other ' end
2. Case when in the statement in different positions in the use
2.1 SELECT Case when usage
Copy Code code as follows:
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.2 WHERE Case Usage
Copy Code code 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 usage
Copy Code code as follows:
SELECT
Case when salary <= THEN ' 1 '
When salary > Salary <= THEN ' 2 '
When salary > Salary <= THEN ' 3 '
When salary > Salary <= 1000 THEN ' 4 '
ELSE NULL End Salary_class,--alias name
COUNT (*)
From Table_a
GROUP by
Case when salary <= THEN ' 1 '
When salary > Salary <= THEN ' 2 '
When salary > Salary <= THEN ' 3 '
When salary > Salary <= 1000 THEN ' 4 '
ELSE NULL end;
3. Other implementations of If-then-else
3.1 DECODE () function
Copy Code code as follows:
Select decode (Sex, ' M ', ' Male ', ' F ', ' Female ', ' Unknown ')
From employees;
It appears that only Oracle provides this function, and does not support ANSI SQL, the syntax is not clear when the case, the individual does not recommend use.
3.2 Special implementations in the Where
Copy Code code 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 under special circumstances, to pay more attention to logic, do not make mistakes.
