SQL case statement nesting

Case has two formats. Simple case functions and case search functions.
1. Simple case Functions

 

Case sex when '1' then' male 'when' 2' then' female 'else' others' end

 

2. Case search functions

 

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' additional '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: (to make it clearer, I have not used a country.CodeInstead, 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:

Select sum (Population ), case country when 'China' then' Asia 'when' India 'then' Asia 'when' Japan 'then' Asia 'when' USA 'then' North America 'when' Canada 'then' north America 'when' Mexico 'then' North America 'else' miscellaneous 'end from table_a group by case country when' China 'then' Asia 'when' India 'then' Asia 'when' Japan 'then' Asia 'when' USA 'then' North America 'when' Canada 'then' North America 'when' Mexico 'then' North America 'else' other 'ends; 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; 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_agroup 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. Sex population (Population) China 1340 China 2260 us 145 us 255 us 151 Canada 249 Canada 140 UK 260 UK by country and gender, the result is as follows: men and women in China, 340260 in the United States, 4555 in Canada, 5149 in the United Kingdom, 4060. In normal cases, union can also be used to query data using a single 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: 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_agroup by country; then we use select to complete the output form of the orders table, this fully demonstrates the power of the Case function. 3. Use the case function in check. Using the case function in check is a good solution in many cases. There may be a lot of people who don't need check at all, so I suggest you try to use check in SQL after reading the example below. The following is an example of Company A, which stipulates that the salary of a female employee must be higher than 1000 RMB. If you use check and case, the following constraint check_salary check (case when sex = '2' then case when salary> 1000 then 1 else 0 end else 1 end = 1) if you simply use check, as shown in the following constraint check_salary check (sex = '2' and salary> 1000), the conditions of the female staff are met, and the male staff cannot enter it.