SQL case functions and detailed descriptions

The following is an example of using the case function to complete this function.

Case has two formats. Simple case functions and case search functions.
-- 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

These two methods can achieve the same function. Simple case functions are relatively simple in writing, but compared with case search functions, there are some functional limitations, such as writing case functions.
Note that the case function returns only the first value that meets the condition, and the rest of the case will be automatically ignored.
-- For example, in the following SQL statement, you will never get the result of the second type.

Case when col_1 in ('A', 'B') then' first class'
When col_1 in ('A') then' Second Class'
Else 'others' end

See some examples below

Select country,
Sum (case when sex = '1' then
Population else 0 end), -- male population
Sum (case when sex = '2' then
Population else 0 end) -- female population
From table_a
Group by country;

Select sum (population ),
Case country
When 'China' then 'Asian'
When 'India 'then' Asia'
When 'Japan 'then' Asia'
When 'American 'then' North American'
When 'Canada 'then' North America'
When 'Mexico 'then' North America'
Else 'others' end
From table_a
Group by case country
When 'China' then 'Asian'
When 'India 'then' Asia'
When 'Japan 'then' Asia'
When 'American 'then' North American'
When 'Canada 'then' North America'
When 'Mexico 'then' North America'
Else 'others' end;

Case 2

 

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
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;