Turn: SQLServerCase has two formats: Simple Case function and Case search function

Source: Internet
Author: User
Reposted from: blog. csdn. netIBM_hoojoarticledetails5546868Case has two formats. Simple Case functions and Case search functions. -- Simple Case function CASEsexWHEN1THEN male WHEN2THEN female ELSE other END -- Case search function CASEWHE

Reprinted from: http://blog.csdn.net/IBM_hoojo/article/details/5546868 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 WHE

Reprinted from: http://blog.csdn.net/IBM_hoojo/article/details/5546868

CaseWithTwo TypesFormat.SimpleCaseFunctionAnd CaseSearchFunction.
--SimpleCaseFunction
CASE sex
WHEN '1' THEN 'male'
WHEN '2' THEN 'female'
ELSE 'others' END
-- CaseSearchFunction
Case when sex = '1' THEN 'male'
WHEN sex = '2' THEN 'female'
ELSE 'others' END

These two methods can achieve the same function.SimpleCaseFunctionThe statement is relatively concise,SearchFunctionIn comparison, there will be some functional limitations, such as writing.
There is another issue to be aware of, CaseFunctionOnly the first qualified value is returned, and the remaining Case is 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

Next let's take a look at how to use CaseFunctionEverything can be done.

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 CaseFunctionThe SQL code is as follows:
Select sum (population ),
CASE country
WHEN 'China' THEN 'Asian'
WHEN 'India 'then' Asia'
WHEN 'Japan 'then' Asia'
WHEN 'American 'then' North American'
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' Asia'
WHEN 'Japan 'then' Asia'
WHEN 'American 'then' North American'
WHEN 'Canada 'then' North America'
WHEN 'Mexico 'then' North America'
ELSE 'others' END;

Similarly, we can use this method to determine the wage level and count the number of people at each level. The SQL code is as follows;
SELECT
Case when salary <= 500 THEN '1'
WHEN salary> 500 AND salary <= 600 THEN '2'
WHEN salary> 600 AND salary <= 800 THEN '3'
WHEN salary> 800 AND salary <= 1000 THEN '4'
Else null end salary_class,
COUNT (*)
FROM Table_A
GROUP
Case when salary <= 500 THEN '1'
WHEN salary> 500 AND salary <= 600 THEN '2'
WHEN salary> 600 AND salary <= 800 THEN '3'
WHEN salary> 800 AND salary <= 1000 THEN '4'
Else null end;

2. Use an SQL statement to group different conditions.

The following data is available:
Country sex population (population)
China 340
China 260
US 1 45
US 2 55
Canada 1 51
Canada 2 49
UK 1 40
UK 2 60

Group by country and gender. The result is as follows:
National male
China 340 260
US 45 55
Canada 51 49
UK 40 60

In normal cases, UNION can also be used to query with a statement. However, this will increase the consumption (two Select clauses), and the SQL statement will be relatively long.
The following is a CaseFunctionTo complete this function.
SELECT country,
SUM (case when sex = '1' THEN
Population ELSE 0 END), -- male population
SUM (case when 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 orders table, fully displaying the CaseFunctionPowerful.

3. Use Case in CheckFunction.

Use Case in CheckFunctionIn many cases, it is a very good solution. There may be a lot of people who don't need Check at all, so I suggest you try to use Check in SQL after reading the example below.
The following is an example.
Company A, the company has A rule that the wages of female employees must be higher than 1000 yuan. If you use Check and Case
CONSTRAINT check_salary CHECK
(Case when sex = '2'
Then case when salary> 1000
THEN 1 ELSE 0 END
ELSE 1 END = 1)

If you simply use Check, as shown below:
CONSTRAINT check_salary CHECK
(Sex = 's2' AND salary> 1000)

The conditions of the female employee are met, and the male employee cannot enter the information.

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.