How to use case in SQL Server (up and down) 1th/2 page _mssql

Source: Internet
Author: User
Copy Code code as follows:

--Simple Case function
Case Sex
When ' 1 ' THEN ' male '
When ' 2 ' THEN ' woman '
Else ' other ' end
--case search function
case when sex = ' 1 ' THEN ' man '
When sex = ' 2 ' THEN ' female '
Else ' other ' end

In both of these ways, you can achieve the same functionality. 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.
Copy Code code as follows:

For example, the following SQL, you can never get the "second class" result
Case when col_1 in (' A ', ' B ') THEN ' first class '
When Col_1 in (' a ') THEN ' Class II '
Else ' other ' end

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

First, the known data is grouped and analyzed in a different way.

Have the following data: (in order to see more clearly, I did not use the country code, but directly using the country name as primary Key)

Country (country) Population (population)
China 600
United States 100
Canada 100
United Kingdom 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. Should get the following result.

Chau Population
Asia 1100
North america 250
Other 700


What would you do to solve the problem? Creating a view with state code is a solution, but it is difficult to dynamically change the way statistics are used.
If you use the case function, the SQL code is as follows:

Copy Code code as follows:

SELECT SUM (population),
Case Country
When ' China ' THEN ' Asia '
When ' India ' THEN ' Asia '
When ' Japan ' THEN ' Asia '
When ' America ' THEN ' North America '
When ' Canada ' THEN ' North America '
When ' Mexican ' THEN ' North America '
Else ' other ' end
From Table_a
GROUP by Case Country
When ' China ' THEN ' Asia '
When ' India ' THEN ' Asia '
When ' Japan ' THEN ' Asia '
When ' America ' THEN ' North America '
When ' Canada ' THEN ' North America '
When ' Mexican ' THEN ' North America '
Else ' other ' end;


Similarly, we can use this method to determine the level of wages, and statistics of the number of each level. The SQL code is as follows;
Copy Code code as follows:

SELECT
Case when salary <= THEN ' 1 '
When salary > Salary <= THEN ' 2 '
When salary > Salary <= THEN ' 3 '
When salary > Salary <= 1000 THEN ' 4 '
ELSE NULL End Salary_class,
COUNT (*)
From Table_a
GROUP by
Case when salary <= THEN ' 1 '
When salary > Salary <= THEN ' 2 '
When salary > Salary <= THEN ' 3 '
When salary > Salary <= 1000 THEN ' 4 '
ELSE NULL end;

Two, use a SQL statement to complete the grouping of different conditions.

Have the following data

Country (country) Sex (Sex) Population (population)
China 1 340
China 2 260
United States 1 45
United States 2 55
Canada 1 51
Canada 2 49
United Kingdom 1 40
United Kingdom 2 60


Grouped by country and gender, the results are as follows

Countries Man Woman
China 340 260
United States 45 55
Canada 51 49
United Kingdom 40 60


In general, the Union can also be implemented with a statement to query. But that increases consumption (two select parts), and the SQL statement is longer.
Here is an example of using the case function to complete this function

Copy Code code as follows:

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 two-dimensional table, fully show the powerful case function.
Third, 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.
Now let's take 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
Copy Code code as follows:

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
Copy Code code as follows:

CONSTRAINT check_salary Check
(Sex = ' 2 ' and salary > 1000)

The condition of the female staff was met, and the male staff could not enter it.
Current 1/2 page 12 Next read the full text
Related Article

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.