The worst place to case When--then--else--end should be that it is used both in normal fields and in aggregate functions.
Original: http://blog.itpub.net/26451903/viewspace-733526
Case has two formats. Simple case function and case search function.
--case search function (recommended)
Case when = ' 1 ' Then ' male ' when = ' 2 ' Then ' female ' ELSE ' other ' END
--Simple case function (not recommended)
Case Sex when ' 1 ' Then ' male ' when ' 2 ' Then ' female ' ELSE ' other ' 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 result of "type two"
Case whenCol_1inch('a','b') Then 'First Class' whenCol_1inch('a') Then 'Type II'ELSE'other' END
Let's take a look at what you 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 did 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), CaseCountry when 'China' Then 'Asian' when 'India' Then 'Asian' when 'Japan' Then 'Asian' when 'United States' Then 'North America' when 'Canada' Then 'North America' when 'Mexico' Then 'North America'ELSE 'other' END fromtable_aGROUP by CaseCountry when 'China' Then 'Asian' when 'India' Then 'Asian' when 'Japan' Then 'Asian' when 'United States' Then 'North America' when 'Canada' Then 'North America' when 'Mexico' Then '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;
SELECT Case whenSalary<= - Then '1' whenSalary> - andSalary<= - Then '2' whenSalary> - andSalary<= - Then '3' whenSalary> - andSalary<= + Then '4'ELSE NULL ENDSalary_class,COUNT(*) fromtable_aGROUP by Case whenSalary<= - Then '1' whenSalary> - andSalary<= - Then '2' whenSalary> - andSalary<= - Then '3' whenSalary> - andSalary<= + 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 |
US |
1 |
45 |
US |
2 |
55 |
Canada |
1 |
51 |
Canada |
2 |
49 |
UK |
1 |
40 |
UK |
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
SELECTCountry,SUM( Case whenSex= '1' ThenpopulationELSE 0 END),--Male populationSUM( Case whenSex= '2' ThenpopulationELSE 0 END)--female population fromtable_aGROUP byCountry;
In this way, we use Select to complete the output form of the two-dimensional table, which fully shows the strong case function.
Here is a simplified SQL that I have in practice
SelectTrip.id,Max( Case whenvote.date_added is NULL Then timestamp '1970-11-7' Elsevote.date_added) asdate_added fromBtrip_trip as Trip Left Joinvote onTrip.id=vote.trip_idGroup byvote.trip.idOrder by Max( Case whenvote.date_added is NULL Then timestamp '1970-11-7' Elsevote.date_added)desc, trip.idASC
How SQL case is used (GO)