Case application in MSSQL (1) [SQL Server]

Case has two formats: Simple case function and case search function.

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

 

Let's take a look at what we can do with the case function.

1. Known Data is grouped and analyzed in another way.

There are the following data: (for better understanding, I did not use country code, but directly use the country name as the primary key)

 
Country Population (Population)

---------------------------------
China 600
US 100
Canada 100
UK 200
France 300
Japan 250
Germany 200
Mexico 50
India 250

 

The population of Asia and North America is counted based on the population data of this country. The following result is returned.

 
Continent population

------------------------
Asia 1100
North America 250
Other 700

 

What do you do if you want to solve this problem? Generating a view with continent code is a solution, but it is difficult to dynamically change the statistical method. If the case function is used, the SQL code is as follows:

Code

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

Similarly, we can use this method to determine the wage level and count the number of people at each level. The SQL code is as follows:

Code

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

2. Use an SQL statement to group different conditions.

The following data is available:

Country sex population (Population)

-------------------------------------------
China 340
China 260
US 1 45
US 2 55
Canada 1 51
Canada 2 49
UK 1 40
UK 2 60

Group by country and gender. The result is as follows:
National male

------------------------
China 340 260
US 45 55
Canada 51 49
UK 40 60

In normal cases, union can also be used to query with a statement. However, this will increase the consumption (two select clauses), and the SQL statement will be relatively long. The following is an example of using the case function to complete this function:

Code

 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;

In this way, select is used to complete the output form of the explain table, which fully shows the power of the Case function.