Case has two formats. Simple case function and case search function.Simple Case Function CaseSexWhen ' 1 ' Then ' Men 'When ' 2 ' Then ' Women 'Else ' other ' END--case search Functioncase if sex = ' 1 ' Then ' Man ' When sex = ' 2 ' Then ' Women 'Else ' other ' END
The same functionality can be achieved by means of the 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 is col_1 in (' A ', ' B ') then ' first Class 'When Col_1 in (' a ') then ' second class 'Else ' other ' END
Let's take a look at what you can do with the case function.
One, the known data in a different way to group, analysis.
There is 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) according to the country's population data, statistics of Asia and North America population. The following result should be obtained.
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:SELECTSUM (population), CaseCountryWhen ' China ' Then ' Asia 'WHEN ' India ' Then ' Asia 'When ' Japan ' Then ' Asia 'When ' America ' Then ' North America 'When ' Canada ' Then ' North America 'When ' Mexico ' then ' North America 'Else ' other ' END fromTable_aGROUP by CaseCountryWhen ' China ' Then ' Asia ' When ' India ' Then ' Asia 'When ' Japan ' Then ' Asia 'When ' America ' 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 when salary <= ' 1 'When salary > Salary <= 2 'When salary > Salary <= 3 'When salary > Salary <= "4"ELSE NULL END salary_class,--alias nameCOUNT (*) fromTable_aGROUP byCase when salary <= ' 1 'When salary > Salary <= 2 'When salary > Salary <= 3 'When salary > Salary <= "4"ELSE NULL END;
Two, a SQL statement is used to complete the grouping of different conditions.
Have the following data
Grouped according to country and gender, the results are as follows
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 if sex = ' 1 ' then population ELSE 0 END),--Male populationSUM (case if sex = ' 2 ' then population ELSE 0 END)--Female populationFrom table_a GROUP byCountry;
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
CONSTRAINT check_salary Check (case if sex = ' 2 ' then case when salary > THEN1 ELSE 0 End Else 1 End = 1)
If you use check only: CONSTRAINT check_salary check (sex = ' 2 ' and Salary > 1000) The female clerk is in accordance with the conditions, the male staff will not be able to input.
four, according to the conditions of the selected update.
example, there are the following update conditions 1. Salaries of employees over 5000 are reduced by 10%
2. Employees with wages between 2000 and 4600 increase their wages by 15%
It is easy to consider the option to execute two UPDATE statements as follows--Condition 1UPDATE Personnel SETSalary = salary * 0.9WHERESalary >= 5000;--Condition 2UPDATE Personnel SETSalary = salary * 1.15WHERE salary >= andSalary < 4600;
But it's not as simple as it is supposed to be, assuming a personal salary of 5000 bucks. First of all, according to condition 1, wages are reduced by 10% to 4500 of wages. Next run the second SQL, because this person's salary is 4500 in the range of 2000 to 4600, need to increase 15%, and finally this person's salary result is 5175, not only not reduced, but increased. If the reverse is done, then the wage of 4600 will turn into a wage reduction. No matter how absurd the rules are, if you want an SQL statement to implement this function, we need to use the case function. The code is as follows:UPDATEPersonnelSETSalary =Case when salary >=Salary * 0.9When salary >= and Salary < 4600 ThenSalary * 1.15ELSE Salary END;
It is important to note here that the last line of else salary is required, and if there is no such line, the wages of those who do not meet these two conditions will be written as NULL, and that will be a big bad thing. The default value of the else part in the case function is null, which is something to be aware of.
This method can also be used in many places, such as changing the primary key dirty.
In general, to the two data primary key,a and B exchange, need to be temporarily stored, copied, read back the data of the three processes, if you use the case function, everything becomes much simpler.
| P_key |
Col_1 |
Col_2 |
| A |
1 |
Tom |
| B |
2 |
John doe |
| C |
3 |
Harry |
Assuming that you have the data, you need to put the primary key
aAnd
bExchange with each other. Use the case function to implement the code as followsUPDATESomeTableSET p_key = P_key = ' A ' Then ' B 'When P_key = ' B ' Then ' a ' ELSE p_key ENDWHERE P_key in (' A ', ' B ');
The same can be exchanged for two unique keys. It should be noted that if there is a need to exchange the primary key occurs, most of the original design of the table is not in place, it is recommended to check the design of the table is appropriate.
Five, two table data is checked for consistency.
The case function differs from the DECODE function. In the case function, you can use Between,like,is null,in,exists and so on. For example, using in,exists, you can make subqueries to achieve more functionality.
The following example shows that there are two tables, tbl_a,tbl_b, and two tables with KeyCol columns. Now we compare two tables, the data in the KeyCol column in the tbl_a can be found in the data of the KeyCol column in Tbl_b, return the result ' matched ', and if not found, return the result ' unmatched '.
To implement this function, you can use the following two statements--When using inSELECTKeyCol,Case if keycol in (SELECT keycol from tbl_b) Then ' matched 'ELSE ' unmatched ' ENDLabel fromTbl_a;--When using the exists SELECTKeyCol,Case if EXISTS (SELECT * from tbl_b WHERE tbl_a.keycol = tbl_b.keycol) Then ' matched ' ELSE ' unmatched ' ENDLabel fromTbl_a; The results of using in and exists are the same. You can also use not and not EXISTS, but be aware of the null situation at this time.
Six, use the aggregate function in the case function
Suppose you have one of the following tables
| School Number (STD_ID) |
Course ID (class_id) |
Course Name (class_name) |
Major in Flag (MAIN_CLASS_FLG) |
| 100 |
1 |
Economics |
Y |
| 100 |
2 |
Historical studies |
N |
| 200 |
2 |
Historical studies |
N |
| 200 |
3 |
Archaeology |
Y |
| 200 |
4 |
Computer |
N |
| 300 |
4 |
Computer |
N |
| 400 |
5 |
Chemical |
N |
| 500 |
6 |
Mathematical |
N |
Some students choose to take several courses at the same time (100,200) Some students choose only one course (300,400,500). Students taking multiple courses are asked to choose a course as their major, with the major flag written in Y. Students who choose only one course, majoring in flag n (in fact, if written in Y, will not have the following trouble, in order to give examples, please include more).
Now we're going to query this table according to the following two conditions
1. People who take only one course, return the ID of that course
2. The person who takes multiple courses, returns the selected master course ID
The simple idea is to execute two different SQL statements to query.
Condition 1
-Condition 1: Students who have chosen only one course
SELECT std_id, MAX (class_id) as Main_class from StudentClass GROUP by std_id have COUNT (*) = 1;
Execution results 1std_id main_class 300 4 400 5 500 6
Condition 2--Condition 2: Select std_id for multiple courses, class_id as Main_class from studentclass WHERE main_class_flg = ' Y ';
Execution Results 2std_id main_class 100 1 200 3
If you use the case function, we can solve the problem with just one SQL statement, as shown belowSELECTSTD_ID, CaseWhen COUNT (*) = 1--The case of a student who chooses only one course thenMAX (class_id)ELSEMAX (Case if main_class_flg = ' Y ' then class_id ELSE NULL END)END asMain_classFrom StudentClass GROUP bystd_id;
Running results std_id main_class 100 1 200 3 300 4 400 5 500 6 by nesting the case function in the case function and using the case function in the aggregate function, we can easily To solve this problem. Using the case function gives us a greater degree of freedom.
Finally, a reminder to use the CASE function Novice Note do not make the following error
CaseCol_1When 1 Then ' right 'when
NULL and then ' wrong 'END
In this statement, when NULL is always returned to unknown, there will never be a wrong case. Because the actual expression of this sentence means
When col_1 = null, this is a wrong usage, and this time we should choose when Col_1 is null.
Vii. Summary
The maximum benefit of combining select with case is two points, one is the flexible organization format when displaying query results, and the other is the effective avoidance of multiple access to the same table or several tables.
Here is a simple example to illustrate. For example table students (ID, name, birthday, sex, grade), the number of boys and girls required by each grade statistics, the results of the table head for, grade, the number of boys, the number of girls. If you do not need a Select case, in order to show the number of men and women, statistically very troublesome, first determine the grade information, and then according to the grade of boys and girls, and very prone to error.
Use the Select Case when you have the following wording:
SELECT grade, COUNT (case if sex = 1 then 1
ELSE NULL
END) Number of boys,
COUNT (case if sex = 2 then 1
ELSE NULL
END) Number of girls
From students
GROUP by grade;
SQL function Note One, case when
