SQL takes advantage of case and then multiple conditions

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 function and case search function.
--Simple Case function
Case Sex
When ' 1 ' Then ' men '
When ' 2 ' then ' women '
Else ' other ' END
--case search function
case If sex = ' 1 ' Then ' man '
When sex = ' 2 ' Then ' women '
Else ' other ' END

For example:

SELECT ID, name, CJ, (case at CJ < Then ' fail ' when CJ between and all then ' good ' when CJ > "excellent" EN D) as Status
From stud

In both of these ways, the same functionality can be achieved. The simple case function is relatively concise, but there are some limitations in function, such as write-judgement, compared to the search function.
There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored.
--for example, the following SQL, you can never get the result of "type two"
Case is col_1 in (' A ', ' B ') then ' first Class '
When Col_1 in (' a ') then ' second class '
Else ' other ' END

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

One, the known data in a different way to group, analysis.

There is 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)
USA 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. The following result should be obtained.
Continent population
Asia 1100
North America 250
Other 700

What would you do to solve this problem? Creating a view with a continent code is a workaround, but it is difficult to dynamically change the way statistics are used.
If you use the case function, the SQL code is 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 ' Mexico ' 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 ' Mexico ' then ' North America '
Else ' other ' END;

Similarly, we can use this method to judge the salary level, and to count the number of each level. The SQL code is as follows;
SELECT
Case when salary <= ' 1 '
When salary > Salary <= 2 '
When salary > Salary <= 3 '
When salary > Salary <= "4"
ELSE NULL END Salary_class,
COUNT (*)
From Table_a
GROUP by
Case when salary <= ' 1 '
When salary > Salary <= 2 '
When salary > Salary <= 3 '
When salary > Salary <= "4"
ELSE NULL END;

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

Have the following data
Country (country) gender (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 according to country and gender, the results are as follows
Country men and women
China 340 260
United States 45 55
Canada 51 49
United Kingdom 40 60

In general, a Union can also be used to implement a query with a single statement. But that increases the consumption (two select parts), and the SQL statement is longer.
Here is an example of using the case function to accomplish this function
SELECT Country,
SUM (case if sex = ' 1 ' Then
Population ELSE 0 END),--Male population
SUM (case if 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 two-dimensional table, which fully shows the strong case function.

Third, use the case function in check.

Using the case function in check is a very good workaround in many cases. There may be a lot of people who don't have check at all, so I suggest you try using check in SQL after reading the following example.
Let's give an example.
Company A, the company has a rule that female employees must pay more than 1000 yuan. If you use check and case to behave 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
CONSTRAINT check_salary Check
(Sex = ' 2 ' and salary > 1000)

The condition of the female clerk was met, and the male clerk could not enter it.

Select Top State,joinstate,

(Case if state=1 and joinstate=0 then 2 if state=1 and joinstate=1 then 1 else 0 end) as Usestate from UserInfo

(2)

Select id,username,namer= (score<= ') Then ' internship '

When (score> ' "and score<= ') Then ' barefoot Doctors '

When (score> ' + score<= ') Then ' village Hygienist '

When (score> ' "and score<= ') then ' Township medics '

When (score> ' "and score<= ') then ' town Hygienist '

When (score> ' "and score<= ') Then ' physician '

When (score> ' + score<= ') Then ' attending physician '

When (score> ' "and score<= ' 10000 ') then ' deputy Chief physician '

When (score> ' 10000 ' and score<= ' 20000 ') Then ' chief physician '

When (score> ' 20000 ' and score<= ' 50000 ') Then ' health Ambassador '

Else ' Health ambassador ' End), (SELECT count (ID)

From Jk01_yiwen_question

WHERE UserID = Dbo.jk01_Member.ID) as Questionnum

From Jk01_member

SQL takes advantage of case and then multiple conditions