MSSQL case when usage

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 WhenCol_1In('A','B')Then 'First Class'WhenCol_1In('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: (to make it clearer, I have not used a country.CodeInstead, use the country name as the primary key)

country)	Population
China	600
USA	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
Others	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   'Asian'  When   'India'       Then   'Asian'  When   'Japan'       Then   'Asian'  When   'America'       Then  'North America'  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   'Asian'  When   'Japan'       Then   'Asian'  When   'America'       Then   'North America'  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;

 Select  Case   When Salary <= 500 Then   '1'  When Salary & gt; 500 And Salary <= 600Then   '2'  When Salary & gt; 600 And Salary <= 800 Then   '3'  When Salary & gt; 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 & gt; 500 And Salary <= 600 Then   '2'  When Salary & gt; 600 And Salary <= 800 Then   '3'  When Salary & gt; 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	1	340
China	2	260
USA	1	45
USA	2	55
Canada	1	51
Canada	2	49
UK	1	40
UK	2	60

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

Country	Male	Female
China	340	260
USA	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.

SelectCountry, sum (Case WhenSex ='1' ThenPopulationElse0End),-- Male populationSum (Case WhenSex ='2' ThenPopulationElse0End)-- Female populationFromTable_aGroup ByCountry;
 

In this way, select is used to complete the output form of the explain table, which fully shows 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.
Company A, the company has a rule that the wages of female employees must be higher than 1000 yuan. If you use check and case

 
ConstraintCheck_salaryCheck(Case WhenSex ='2'Then Case WhenSalary & gt; 1000Then1Else0EndElse1End= 1)
 

If you simply use check, as shown below:

ConstraintCheck_salaryCheck(Sex ='2' AndSalary> 1000)
 

The conditions of the female employee are met, and the male employee cannot enter the information.