Case multi-condition Query

Use pubs
Create Table electrician and Dongguan table
(Unit: varchar (10 ),
2009 varchar (10 ),
Conference fee: 2009 varchar (10 ),
Transportation fee: 2009 varchar (10 ),
Training fee: 2009 varchar (10 ),
Hospitality fee 2009 varchar (10 ),

2010 varchar (10 ),
Conference fee: 2010 varchar (10 ),
Transportation fee: 2010 varchar (10 ),
Training fee: 2010 varchar (10 ),
Hospitality fee 2010 varchar (10 ),

2011 varchar (10 ),
Conference fee: 2011 varchar (10 ),
Transportation fee: 2011 varchar (10 ),
Training fee: 2011 varchar (10 ),
Hospitality fee 2011 varchar (10 ),

2012 varchar (10 ),
Conference fee: 2012 varchar (10 ),
Transportation fee: 2012 varchar (10 ),
Training fee: 2012 varchar (10 ),
Hospitality fee 2012 varchar (10 ))

Insert into electrician and Dongguan table
Select unit,
Case when year = '000000' and subject = 'country of output' then amount else 0 end,
Case when year = '000000' and subject = 'conference payby' then amount else 0 end,
Case when year = '20140901' and subject = 'transportation payby' then amount else 0 end,
Case when year = '000000' and subject = 'Training' then amount else 0 end,
Case when year = '000000' and subject = 'hospitality 'Then amount else 0 end,

Case when year = '000000' and subject = 'country of output' then amount else 0 end,
Case when year = '000000' and subject = 'conference payby' then amount else 0 end,
Case when year = '20140901' and subject = 'transportation payby' then amount else 0 end,
Case when year = '000000' and subject = 'Training' then amount else 0 end,
Case when year = '000000' and subject = 'hospitality 'Then amount else 0 end,

Case when year = '000000' and subject = 'country of output' then amount else 0 end,
Case when year = '000000' and subject = 'conference payby' then amount else 0 end,
Case when year = '20140901' and subject = 'transportation payby' then amount else 0 end,
Case when year = '000000' and subject = 'Training' then amount else 0 end,
Case when year = '000000' and subject = 'hospitality 'Then amount else 0 end,

Case when year = '000000' and subject = 'country of output' then amount else 0 end,
Case when year = '000000' and subject = 'conference payby' then amount else 0 end,
Case when year = '20140901' and subject = 'transportation payby' then amount else 0 end,
Case when year = '000000' and subject = 'Training' then amount else 0 end,
Case when year = '000000' and subject = 'hospitality 'Then amount else 0 end
From diangong

 

In addition:

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

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

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

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

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
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 below:
Constraint check_salary check
(Sex = 's2' and salary> 1000)

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

 

Again:

Example:
There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):

If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table
 

Case may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can use case in the WHERE clause.
 
First, let's take a look at the case syntax. In a general SELECT statement, the syntax is as follows:
 
Select <mycolumnspec> =
Case
When <A> then <somethinga>
When <B> then <somethingb>
Else <somethinge>
End
 

In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:
 
Use pubs
Go
Select
Title,
'Price range' =
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End
From titles
Order by price
Go
 

This is a typical example of case, but you can do more with case. For example, the case in the group by clause below:
 
Select 'number of tidles ', count (*)
From titles
Group
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End
Go
 

You can even combine these options to add an order by clause, as shown below:
 
Use pubs
Go
Select
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End as range,
Title
From titles
Group
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End,
Title
Order
Case
When price is null then 'unpriced'
When price <10 then 'bargain'
When price between 10 and 20 then 'average'
Else 'Gift to impress relatives'
End,
Title
Go
 

Note: To use case in the group by block, the query statement must repeat the case block in the select block in the group by block.
 
In addition to selecting custom fields, case is useful in many cases. Further, you can get the grouping sorting result set that you previously thought was impossible to get.