SQL case and then else end as usage

Case has two formats. Simple case function and case search function. --Simple case function case sex when ' 1 ' then ' Male ' when ' 2 ' then ' women ' else ' other ' END--case search function case when sex = ' 1 ' the N ' male ' when sex = ' 2 ' Then ' Else ' other ' END these two ways, can achieve the same function. 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 a "second class" When the case is col_1 in (' A ', ' B ') then ' first Class ' When col_1 in (' a ') then ' second class ' ELS E ' 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 did not use the country code, but directly with the country name as primary Key) country (country) population (population) China 600 USA 100 Canada 100 UK 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. Continent Population Asia 1100 North America 250 Other 700 what would you do if you want 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) country when ' 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 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 ' when ' Mexico ' then ' North America Other ' END of the continent ' else '; 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 <= and "3" when salary > 100 and Salary <= 0 Then ' 4 ' ELSE NULL END salary_class, COUNT (*) from table_a GROUP by case when salary <= 50 0 Then ' 1 ' when salary > Salary <= and "2 ' when salary > and Salary &   lt;= ' 3 ' When salary > Salary <= and ' 4 '      ELSE NULL END; Two, a SQL statement to complete the different conditions of the grouping. There are data in countries (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 United Kingdom 2 60 by country and by gender The results are as follows country men and women China 340 260 USA 45 55 Canada 51 49 United Kingdom 40 60 in general, a Union can also be used to query with a single statement. But that increases the consumption (two select parts), and the SQL statement is longer.   The following is an example of using the case function to complete this function, SELECT country, SUM (cases when sex = ' 1 ' then population ELSE 0 END),  --Male population SUM (case if 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 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 take an example of company A, the company has a rule that female employees must pay more than 1000 dollars. If a check and case are used, the CONSTRAINT check_salary check is shown as follows (cases when sex = ' 2 ' and ' then "when SA Lary > then 1 else 0 end else 1 end = 1) If you simply use check, as shown below CONSTRAINT CH Eck_salary CHECK (sex = ' 2 ' AND Salary > 1000) The conditions of the female staff were met, and the male clerk could not enter four, according to the conditions of the selected update. example, there are the following update conditions for employees with wages of more than 5000, wages reduced by 10% wages between 2000 to 4600 employees, the increase in wages 15% is easy to consider is to choose to execute two update statements, as follows-condition 1 update personnel SET salary = Salary * 0.9 WHERE salary >= 5000; --Condition 2 UPDATE personnel SET Salary = salary * 1.15 WHERE salary >= and salary < 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: UPDATE personnel SET salary = case when salary >= to salary * 0.9 when salary >= Salary < 4600 then salary * 1.15 ELSE 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 sheets three B 2 John Doe C 3 Harry assuming the above data, the primary key A and B need to be exchanged. With the case function, the code is as follows: UPDATE SomeTable SET p_key = P_key = ' A ' Then ' B ' when p_key = ' B ' and ' A ' ELSE p_key END WHERE 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 in, select KeyCol, Cases when keycol in (select KeyCol from Tbl_b) and then ' matched ' ELSE ' UNMATC Hed ' END Label from Tbl_a; --When using EXISTS, select KeyCol, Case time EXISTS (SELECT * from tbl_b WHERE tbl_a.keycol = tbl_b.keycol) Then ' matched ' ELS E ' unmatched ' END Label from Tbl_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, the use of the aggregate function in the case function is assumed to have the following table number (STD_ID) Course ID (class_id) course name (class_name) Major in Flag (MAIN_CLASS_FLG) 100 1 Economics Y 100 2 history N 200 2 Calendar Historiography N 200 3 Archaeology Y 200 4 computer n 300 4 computer n 400 5 chemistry n 500 6 math n Some studentsChoose 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 criteria. The person who takes only one course, the ID of the course, and 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: Only one course is selected for students select std_id, MAX (class_id) as Main_class from StudentClass GROUP by std_id have COUNT (*) = 1;  Execution Results 1 std_id main_class----------------300 4 400 5 500 6 Condition 2--Condition 2: Select std_id for Multi-course students, class_id As Main_class from studentclass WHERE main_class_flg = ' Y '; Execution Results 2 std_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 in SELECT std_id When COUNT (*) = 1--The case of a student who chooses only one course then Max (class_id) Else max if main_class_flg = ' Y ' then class_id else NULL END ) END as Main_class from StudentClass GROUP by std_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, the total We can easily solve this problem by using the case function in the function. Using the case function gives us a greater degree of freedom。 At the end of the note, the novice using the case function is careful not to make the following error case Col_1 if 1 then "right" when null and then ' wrong ' END in this statement when NULL this line always returns Unknow N, so there will never be a wrong situation. Because this sentence can be replaced when col_1 = NULL, this is a wrong use, this time we should choose to use when Col_1 is NULL.

SQL case and then else end as usage