How to use the case

We all know when the use of the case, once satisfied with a certain when, then this data will be withdrawn when, and no longer consider other case, the article detailed introduction of the case when the use and examples to explain. 　　  Case use-simple case function   Simple case expression, using an expression to determine the return value. Syntax:  www.2cto.com     Code as follows search_expression when expression1 THEN result1 when Expression2 THEN 　　Result2 ... 　　When Expressionn THEN resultn ELSE default_result   Search CASE expression, use criteria to determine the return value. 　　Syntax:   code as follows when Condition1 THEN RESULT1 when Condistion2 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 then ' when product_type_id=3 then ' DVD ' when product_type_id=4 t The same functionality can be achieved by hen ' CD ' Else ' Magazine '  www.2cto.com   End from the products   in both ways. 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, the following SQL, you can never get "second class" This result     code as follows   case when col_1 in (' A ', 'B ') THEN ' first class ' When col_1 in (' a ')       THEN ' second class ' Else ' other ' end   below Let's take a look at what we can do with the case function.   One, the known data are grouped and analyzed in a different way.   has 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   According to the population data of this country, the population numbers of Asia and North America are counted. Should get the following result. Continent Population Asia 1100 North America 250 other   want to 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.  www.2cto.com   If you use the case function, the SQL code is as follows     code as follows SELECT SUM (population), country when ' China '   &nbsp ; THEN ' Asia ' when ' India '     THEN ' Asia ' when ' Japan '     THEN ' Asia ' when ' us '     THEN ' North America ' when ' Canada ' THEN ' North America ' when ' Mexican ' THEN ' North America ' Else ' other ' end of ' from    table_a GROUP by case country ' China '     T HEN ' 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 ' end;   Similarly, we can use this method to judge the level of wages, and statisticsThe number of each level. The SQL code is as follows     code is salary <= THEN ' 1 ' When salary > Salary ' 2 ' <= EN Salary > Salary <= THEN ' 3 ' When salary > I-Salary <= 1000 ' 4 ' ELSE NULL end THEN Class, COUNT (*) from    table_a GROUP by case salary <= THEN ' 1 ' when salary > and salary ; = THEN ' 2 ' when salary > Salary <=-THEN ' 3 ' when salary > and Salary <= 1000-THEN ' 4 ' ELS E NULL end;  www.2cto.com     Two, with an SQL statement to complete the grouping of different conditions.   has the following data countries (country) sex (sex) population (population) China 1 340 China 2 260 USA 1 45 USA 2 55 Canada 1 51 Canada 2 49 UK 1 40 UK 2   Grouped by country and gender, the results are as follows: China 340 260 USA 45 55 Canada 51 49 UK   Under normal circumstances, the Union can also be implemented with a statement query. But that increases consumption (two select parts), and the SQL statement is longer. The following is a case function to complete the function of the example     code as follows SELECT country, sum (cases when sex = ' 1 ' THEN population ELSE 0),--Male population SUM (Case of sex = ' 2 ' THEN population ELSE 0 end)  --FemaleSex population from table_a GROUP by country;   So we use Select to complete the output form of two-dimensional table, fully show the powerful case function.   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 check at all, so I suggest you try using check in SQL after looking at the example below.   We'll give you 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  www.2cto.com       code below CONSTRAINT check_salary check (cases when sex = ' 2 ' TH EN Case When salary > 1000 THEN 1 Else 0 "Else 0 End"   If simply using check, the following     code is as follows CONSTRAINT Check_sa Lary CHECK (sex = ' 2 ' and Salary > 1000)   The condition of the female staff is met, the male staff can not enter. Instance   code is as follows CREATE TABLE Feng_test (ID number, Val varchar2), insert into feng_test (Id,val) VALUES (1, ' ABCDE '); Insert I Nto feng_test (Id,val) VALUES (2, ' abc '); Commit Sql>select * from Feng_test; ID            val-------------------1             ABCDE 2 &N Bsp           ABC sql>select ID     &NBSP; When the case is Val like ' a% ' then ' 1 '           when Val like ' abcd% ' then ' 2 '      else ' 999 '  www.2cto.com & nbsp     End case from Feng_test; ID             Case---------------------1                  1 2                  1 based on my own experience, I'd say that when using case, this looks like an ASP case WH En to the use of SWICTH case development in PHP, as long as there is a basic knowledge that I think the case in SQL is actually very good understanding.