The use of case--simple case function simple case expression, using an expression to determine the return value. Syntax: Case search_expression while expression1 then RESULT1 when expression2 Then result2 ... When Expressionn then Resultn ELSE default_result--The search case expression uses the condition to determine the return value. Syntax: When the case is Condition1 then result1 when Condi Stion2 then result2 ... When Condistionn then Resultn ELSE Default_result End Example: Select product_id,product_type_id, Case when product_type_id= 1 Then "book" When Product_type_id=2 and ' Video ' when product_type_id=3 then ' DVD ' when product_type_id=4 then ' CD ' E The LSE ' Magazine ' end from the products are two ways to achieve the same functionality. 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.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. www.2cto.com If you use the case function, the SQL code is as follows code as follows select SUM (population) countrywhen ' 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 ' Else ' other ' endfrom table_agroup by case Countrywhen ' China ' the N ' Asia ' when ' India ' Then ' Asia ' when ' Japan ' Then ' Asia ' when ' us ' then ' North America ' when ' Canada ' then ' North America ' when ' Mexico ' then ' else ' other ' end; Similarly, we can use this method to judge the salary level and count the number of people at each level. The SQL code is as follows code as follows selectcase when salary <= "1 ' When salary >" Salary <= "then ' 2" when Sal ary > Salary <= 3 ' when salary > and salary<= ' 4 ' ELSE NULL END salary_class,count (*) from table_agroup bycase when salary <= ' 1 ' When salary > Salary <= 2 "when salary > and salary <=, then ' 3 ' when salary > + A ND salary <= ' 4 ' ELSE NULL END; www.2cto.com Two, use an SQL statement to complete the grouping of different conditions. has the following data countries (country) gender (sex) population (population) China 1 340 China 2 260 US 1 45 United States 2 55 Canada 1 51 Canada 2 49 UK 1 40 UK 2 60 Group by country and gender , the results are as follows: Women and men China 340 260 USA 45 55 Canada 51 49 UK 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 code as follows select Country,sum (cases when sex = ' 1 ' thenpopulation ELSE 0 END),--Male population SUM When sex = ' 2 ' thenpopulation ELSE 0 END) --female population from Table_agroup by country; so we use Select to complete the output form of the two-dimensional table, fully display the C The ASE function is powerful. Three, 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 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, which has a rule that female employees must pay more than 1000 dollars. If you use check and case toAs shown below, the www.2cto.com code is as follows constraint check_salary check (case if sex = ' 2 ' then cases when salary > 1000THEN 1 ELSE 0 endelse 0 END) If you simply use check, as shown below code constraint check_salary check (sex = ' 2 ' and Salary > F) The conditions of the female staff were met, and the male clerk was unable to enter. Example code as below CREATE TABLE Feng_test (ID number, Val varchar2), insert into feng_test (Id,val) VALUES (1, ' ABCDE '); insert Into Feng_test (Id,val) VALUES (2, ' abc '); commit; Sql>select * from Feng_test;id val-------------------1 &N Bsp Abcde2 Abcsql>select id ' a% ' then ' 1 ' If Val like ' abcd% ' then ' 2 ' else ' 999 ' www.2c to.com End Casefrom feng_test;id Case---------------------1 12 1 based on my own experience, I think it's a lot like ASP when I use case. Case is used in PHP swicth case to develop the usage of the statement, as long as a little basic know I think the case in SQL is actually very well understood.
