Execution order & nesting use of case

Let's look at the data in the table first:

Select *  from  ProductInfo

PRODUCTID PRODUCTNAME productprice QUANTITY CATEGORY desperation ORIGINPID0001 T-shirt25.62          -Clothing Promotional Products Beijing PID0002 Wok35.62          -Kitchenware Promotional Products Beijing PID0003 Watermelon45.62          -Fruit Promotion Products Beijing PID0004 perch55.62          -Fish Product Description 004 Beijing PID0004 Crucian Carp75.62          -Fish Product Description 005 Beijing PID0004 Grass Carp65.62          -Fish Product Description 006 Beijing

Demand:

The non-fish products are marked as 99, the perch is labeled 2, and the non-perch fish is labeled 50.

steps:First step: Use nested case statements to mark non-fish as

(Case   when = ' Fish '  then   1 Else 0  End = 0  Then '  About '

The second step: In fish, the name is ' perch ' marked asstep three: After the first second step, the remaining mark is 50, that is, the non-perch fish is marked as a.

The overall SQL statement is as follows:

SelectPRODUCTNAME, CATEGORY, Case          　　　　  when( Case  whenCategory= 'Fish'  Then 1  Else 0 End)= 0  Then ' About'          　　　　  whenProductName= 'Perch'  Then '2'          　　　　 Else ' -'        　　 EndCategory_code fromProductInfo

The results of the implementation are as follows:

PRODUCTNAME    CATEGORY        category_codet shirt            dress             -up  Wok                kitchen             water  melon                Fruit              About perch                Fish                              2              carp fish fish               -            

Execution Order Description:

From the first when condition starts down, and if the condition is met, the following when condition is not executed.

Give me a chestnut:

SelectPRODUCTNAME, CATEGORY, Case          　　　　  when( Case  whenCategory= 'Fish'  Then 1 Else  0 End)= 0  Then ' About'          　　　　  whenProductName= 'Perch'  Then '2'          　　　　  whenProductName= 'Watermelon'  Then ' the'         　　 　　 Else ' -'        　　 EndCategory_code fromProductInfo

What do you think the ultimate category_code of watermelon will be? Will it be ' 52 ' in the code?

The answer is no, the watermelon in the first when the conditional statement, has been given the ' 99 ', it is over, will not execute the following when conditions. Let's look at the results:

 productname CATEGORY category_codet shirt clothes  9 9   Wok cookware  99   watermelon fruit  99   bass fish  2   Crucian carp fish  100   grass carp fish  100   

Watermelon is still ' 99 '.

Above

Execution order & nesting use of case