How to Use Case in SQL (Part 2)

Next

4. Conditional update.

For example, the following update conditions are available:

1. For employees with a salary of more than 5000, the salary is reduced by 10%.
2. Employees with salaries between 2000 and 4600 increase by 15%

You can easily choose to execute two update statements, as shown below:

 
-- Condition 1UpdatePersonnelSetSalary = salesary * 0.9WhereSalary >=5000;-- Condition 2UpdatePersonnelSetSalary = salesary * 1.15WhereSalary> = 2000AndSalary <1, 4600;
 

However, it is not as simple as you think. Suppose there is a personal salary of 5000 RMB. First, according to condition 1, the salary is reduced by 10% to 4500. Next, when we run the second SQL statement, because the salary of this person is within the range of 4500 to 2000, we need to increase by 4600. Finally, the salary of this person is 15%, which is not only not reduced, instead, it increases. If this is done in turn, 4600 of the employees will be reduced. No matter how absurd this rule is, if you want an SQL statement to implement this function, we need to use the case function.CodeAs follows:

 
UpdatePersonnelSetSalary =Case WhenSalary> = 5000ThenSalary * 0.9WhenSalary> = 2000AndSalary <1, 4600ThenSalary * 1.15ElseSalaryEnd;
 

note that the else salary in the last row is required. If there is no such line, the salary of the person who does not meet the two conditions will be written as null, that's a bad deal. In the case function, the default value of the else part is null, which is worth attention.
This method can also be used in many places, such as changing the primary key.
generally, to exchange the primary key, A, and B of the two data, you need to temporarily store, copy, and read the data back, if you use the case function, everything becomes much easier.

p_key	col_1	col_2
A	1	James
B	2	Li Si
C	3	Wang Wu

Assume that the primary key must beAAndBExchange. The Code is as follows:

 
UpdateSometableSetP_key =Case WhenP_key ='A'Then 'B'WhenP_key ='B'Then 'A'ElseP_keyEndWhereP_keyIn('A','B');
 

You can also exchange two unique keys. It should be noted that if primary keys need to be exchanged, most of the reasons are that the design of the table was not enough. We recommend that you check whether the table is properly designed.

5. Check whether the data of the two tables is consistent.

The case function is different from the decode function. In the case function, between, like, is null, In, exists, and so on can be used. For example, you can use in and exists to perform subqueries to implement more functions.
Two tables, tbl_a and tbl_ B, both of which have keycol columns. Now we compare the two tables. If the data in the keycol column of tbl_a can be found in the data in the keycol column of tbl_ B, the return result is 'matched'. If not, the returned result is 'unmatched '.
To implement the following functions, you can use the following two statements:

 -- When Using in  Select Keycol, Case   When Keycol In ( Select Keycol From Tbl_ B) Then   'Matched'  Else  'Unmatched'   End Label From Tbl_a; -- When Using exists  Select Keycol, Case   When Exists ( Select * From Tbl_ B Where Tbl_a.keycol = tbl_ B .keycol) Then   'Matched'  Else   'Unmatched'  End Label From Tbl_a;

The results of using in and exists are the same. You can also use not in and not exists, but pay attention to null.

6. Use the aggregate function in the case Function

Assume that the following table exists.

Student ID (std_id)	Course ID (class_id)	Course name (class_name)	Major: flag (main_class_flg)
100	1	Economics	Y
100	2	History	N
200	2	History	N
200	3	Archaeology	Y
200	4	Computer	N
300	4	Computer	N
400	5	Chemistry	N
500	6	Mathematics	N

Some students choose to take several courses (100,200) at the same time, and some students choose only one course (300,400,500 ). If you are a student of multiple courses, You must select a course as your major. The major is flag, which is written into Y. Students who only select one course, whose major is flag is n (in fact, if I write y, there will be no troubles below. For example, please include more ).
Now we need to query the table according to the following two conditions:

1. The person who takes only one course returns the ID of the course.
2. Returns the ID of the selected primary course.

The simple idea is to execute two different SQL statements for query.
Condition 1

 
-- Condition 1: only students who have selected one courseSelectStd_id, max (class_id)AsMain_classFromStudentclassGroup ByStd_idHavingCount (*) = 1;
 

Execution result 1

 
Std_id main_class----------------300 4400 5500 6
 

Condition 2

-- Condition 2: Select multiple course studentsSelectStd_id, class_idAsMain_classFromStudentclassWhereMain_class_flg ='Y';
 

Result 2

 
Std_id main_class----------------100 1200 3
 

If the case function is used, we only need one SQL statement to solve the problem, as shown below:

SelectStd_id,Case WhenCount (*) = 1-- Select only one course studentThenMax (class_id)ElseMax (Case WhenMain_class_flg ='Y'ThenClass_idElse Null End)End AsMain_classFromStudentclassGroup ByStd_id;
 

Running result

Std_id main_class----------------100 1200 3300 4400 5500 6
 

By embedding case functions in case functions and using case functions in aggregate functions, we can easily solve this problem. The use of the Case function gives us greater freedom.
Finally, I would like to remind the novice who uses the case function not to make the following mistakes.

 
CaseCol_1When1Then 'Right'When NullThen 'Wrong'End
 

In this statement, the when Null Line always returns unknown, so wrong will never occur. This statement can be replaced with when col_1 = NULL. This is an incorrect usage. In this case, we should use when col_1 is null.

Address: http://www.cnblogs.com/Ronin/archive/2006/07/20/455756.html