Case has two formats. Simple case function and case search function.
--Simple Case functionCase SexWhen' 1 'then man ' when ' 2 ' then female ' else ' then ' man ' when sex = ' 2 ' then female ' end
In both of these ways, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function.
There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored.
For example, the following SQL, you can never get the "second class" This result ' First class ' in(' Second class 'ELSEEND
Let's look at what we can do with the case function.
One, the known data is grouped in another way, analyzed.
has the following data: (in order to see more clearly, I do not use the country code, but directly with the country name as primary Key)
Country (country) |
Population (population) |
China |
600 |
United States |
100 |
Canada |
100 |
United Kingdom |
200 |
France |
300 |
Japan |
250 |
Germany |
200 |
Mexico |
50 |
India |
250 |
According to the population data of this country, the population of Asia and North America is counted. The following result should be obtained.
Chau |
Population |
Asia |
1100 |
North america |
250 |
Other |
700 |
What would you do to solve this problem? Creating a view with a continent code is a workaround, but it is difficult to dynamically change the way statistics are used.
If you use the case function, the SQL code is as follows:
SELECT SUM (population),Case CountryWhenChinaThenAsiaWhenIndiaThenAsiaWhenJapanThenAsiaWhenUnited StatesThenNorth AmericaWhenCanadaThenNorth AmericaWhenMexicoThenNorth AmericaELSEOtherENDFrom Table_aGROUPByCase CountryWhenChinaThenAsiawhen India ' then ' Asian ' then Span style= "color: #800080;" > ' Asian ' then Span style= "color: #800080;" > ' North America ' then Span style= "color: #800080;" > ' North America ' then Span style= "color: #800080;" > ' North America ' else ' other ' end;
Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows;
SELECTCaseWhen salary <= 500Then' 1 'When salary > 500and salary <= 600Then' 2 'When salary > 600and Salary <= 800Then' 3 'When salary > 800and Salary <= 1000Then' 4 'ELSENullEND Salary_class,count (*)From Table_agroup bycase when salary <= then ' 1 ' when salary > and salary <= "then ' 2 ' when Salary > 600 and salary <=, then ' 3 ' and salary <= then ' 4 ' else null end;
Two, a SQL statement is used to complete the grouping of different conditions.
Have the following data
Country (country) |
Gender (Sex) |
Population (population) |
China |
1 |
340 |
China |
2 |
260 |
United States |
1 |
45 |
United States |
2 |
55 |
Canada |
1 |
51 |
Canada |
2 |
49 |
United Kingdom |
1 |
40 |
United Kingdom |
2 |
60 |
Grouped according to country and gender, the results are as follows
Countries |
Man |
Woman |
China |
340 |
260 |
United States |
45 |
55 |
Canada |
51 |
49 |
United Kingdom |
40 |
60 |
In general, a Union can also be used to implement a query with a single statement. But that increases the consumption (two select parts), and the SQL statement is longer.
Here is an example of using the case function to accomplish this function
select country,sum (case when sex = ' 1 ' else 0 end), --male population sum (case when Sex = ' 2 ' thenpopulation else 0 --female population from table_agroup by country;
In this way, we use Select to complete the output form of the two-dimensional table, which fully shows the strong case function.
third, use the case function in check.
Using the case function in check is a very good workaround in many cases. There may be a lot of people who don't have check at all, so I suggest you try using check in SQL after reading the following example.
Let's give an example.
Company A, the company has a rule that female employees must pay more than 1000 yuan. If you use check and case to behave as follows
' 2 ' whensalary > Endend = 1)
If you simply use check, as shown below
and Salary > 1000)
The condition of the female clerk was met, and the male clerk could not enter it.
How SQL case is used