Case has two formats. Simple case function and case search function. Simple case function Case sex when ' 1 ' THEN ' man ' when ' 2 ' THEN ' Else ' other ' end--case search function case when sex = ' 1 ' THEN ' man ' when sex = ' 2 ' THEN ' woman ' Else ' other ' end
Ways to achieve the same functionality. The simple case function is relatively concise, but there are some limitations to the function, such as writing a judgment, compared with the search function. There is also a problem to note that the case function returns only the first qualifying value, and the remainder of the case is automatically ignored. For example, in the following SQL, you can never get the "second class" result when col_1 in (' A ', ' B ') THEN ' first class ' When col_1 in (' a ') THEN ' second ' else ' other ' end
Let's take a look at what we can do with the case function.
first, the known data is grouped and analyzed in a different way.
The following data are available: (in order to see more clearly, I do not use the country code, but directly using the country name as primary Key) according to the country's population data, the number of people in Asia and North America. Should get the following result.
Solve this problem, what would you do. Creating a view with state code is a solution, 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), country when ' China ' THEN ' Asia ' WH EN ' India ' THEN ' Asia ' when ' Japan ' THEN ' Asia ' when ' Us ' THEN ' North America ' when ' Canada ' THEN ' North America ' when ' Mexico ' THEN ' North America ' Else ' other ' end of ' from Table_a GROUP By case country when ' China ' THEN ' Asia ' when ' India ' THEN ' Asia ' when ' Japan ' THEN ' Asia ' when ' Us ' THEN ' North America ' when ' Canada ' THEN ' North America Chau ' when ' Mexico ' THEN ' North America ' Else ' other ' end;
Similarly, we can use this method to determine the level of wages, and statistics of the number of each level. The SQL code is as follows: SELECT case when salary <= THEN ' 1 ' When salary > Salary <= ' 2 ' when THEN ; Salary <= THEN ' 3 ' when salary > a salary <= 1000 ' 4 ' ELSE NULL end THEN--alias Named COUNT (*) from table_a GROUP by case salary <= THEN ' 1 ' when salary > and salary <= T HEN ' 2 ' when salary > Salary <=-THEN ' 3 ' when salary > and Salary <= 1000 ' 4 ' ELSE THEN LL end;
Two, use a SQL statement to complete the grouping of different conditions.
Have the following data
Grouped by country and gender, the results are as follows
In general, the Union can also be implemented with a statement to query. But that increases consumption (two select parts), and the SQL statement is longer. Here 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, we use Select to complete the output form of two-dimensional table, fully show the powerful case function.
third, use the case function in check.
Using the case function in check is a very good solution in many cases. There may be a lot of people who don't check at all, so I suggest you try using check in SQL after looking at the example below.
Now let's take an example
Company A, the company has a rule, the female staff must pay more than 1000 yuan. If you use check and case to behave, as shown below
CONSTRAINT check_salary Check (case when sex = ' 2 ' THEN case when salary > 1000 THEN1 ELSE 0 end Else 1 end = 1)
If the condition of the female staff is check:constraint check_salary check (sex = ' 2 ' and Salary > 1000), the male staff cannot enter it.
four, depending on the condition of the selected update.
For example, there are the following update conditions 1. Staff with a salary of more than 5000, the salary is reduced by 10%
2. Employees with a salary increase of 2000 to 4600. It is easy to consider the option to execute the two UPDATE statement as follows-Conditions 1 update personnel SET salary = salary * 0.9 WHERE Salary & Gt;= 5000; --Condition 2 UPDATE personnel SET Salary = salary * 1.15 WHERE salary >= and salary < 4600;
But it was not as simple as it was supposed to be, assuming there was a personal salary of 5000 dollars. First, according to the condition 1, the salary is reduced 10%, becomes the salary 4500. Next run the second SQL, because this person's salary is 4500 in the range of 2000 to 4600, need to increase by 15%, finally this person's salary result is 5175, not only did not reduce, but increased. If it were to be done in reverse, a 4600 of the wage would turn into a reduced wage. Regardless of how absurd this regulation is, if you want an SQL statement to implement this function, we need to use the case function. The code is as follows: UPDATE personnel SET salary = case when salary >= 5000 THEN salary * 0.9 when salary > = 4600 and Salary < THEN salary * 1.15 ELSE salary end;
It is important to note here that the last line of else salary is required, and if this is not the case, the wages of those who do not meet these two conditions will be written null, and that would be a bad thing. The default value for the else part in the case function is null, and this is where you need to be aware.
This method can also be used in many places, such as changing the primary key such dirty.
In general, to two data primary Key,a and B exchange, the need for temporary storage, copy, read back the data of the three processes, if the use of case functions, everything becomes much simpler.
| P_key |
Col_1 |
Col_2 |
| A |
1 |
Tom |
| B |
2 |
John doe |
| C |
3 |
Harry |
Suppose you have data, you need to exchange primary keys A and B. With the case function to implement, the code follows the UPDATE sometable SET p_key = when p_key = ' a ' THEN ' B ' when p_key = ' B ' THEN ' a ' ELSE
