SQL uses case when then multi-condition judgment

Source: Internet
Author: User
SQL uses case when then multi-condition judgment

Case
   When condition 1 then result 1
   When condition 2 then result 2
   When Condition 3 then result 3
   When condition 4 then result 4
.........
   When condition n then result n
   Else result x
End

Case has two formats. Simple case functions and case search functions.
-- Simple case Function
Case sex
        When '1' then 'male'
        When '2' then 'female'
Else 'others' end
-- Case search function
Case when sex = '1' then 'male'
        When sex = '2' then 'female'
Else 'others' end

For example:

Select   ID, name, CJ, (case when CJ <60 then 'failed' when CJ between 60 and 90 then' good 'when CJ> 90 then' excellent 'end) as status
From Stud

 

These two methods can achieve the same function. Simple case functions are relatively simple in writing, but compared with case search functions, there are some functional limitations, such as writing case functions.
Note that the case function returns only the first value that meets the condition, and the rest of the case will be automatically ignored.
-- For example, in the following SQL statement, you will never get the result of the second type.
Case when col_1 in ('A', 'B') then' first class'
        When col_1 in ('A ')     Then 'second class'
Else 'others' end

Let's take a look at what we can do with the case function.

1. Known Data is grouped and analyzed in another way.

There are the following data: (for better understanding, I did not use country code, but directly use the country name as the primary key)
Country Population (Population)
China 600
US 100
Canada 100
UK 200
France 300
Japan 250
Germany 200
Mexico 50
India 250

The population of Asia and North America is counted based on the population data of this country. The following result is returned.
Continent population
Asia 1100
North America 250
Other 700

What do you do if you want to solve this problem? Generating a view with continent code is a solution, but it is difficult to dynamically change the statistical method.
If the case function is used, the SQL code is as follows:
Select Sum (Population ),
        Case country
                When 'China'   Then 'Asian'
                When 'India'   Then 'Asian'
                When 'Japan'   Then 'Asian'
                When 'America'   Then 'North America'
                When 'Canada' Then 'North America'
                When 'Mexico' Then 'North America'
        Else 'others' end
From   Table_a
Group by case country
                When 'China'   Then 'Asian'
                When 'India'   Then 'Asian'
                When 'Japan'   Then 'Asian'
                When 'America'   Then 'North America'
                When 'Canada' 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.