SQL Server advanced content-Overview and use of case Syntax Functions

1. usage of the Case Function
(1) Use the following commands: switch-case and if-else if.
(2) Syntax:
Case [field]
When expression then display data
When expression then display data
Else display data
End
(3) percent conversion of Quality Education
1) We want to convert the displayed data to ABCDE. The rule is to show A at or above 90 points, B at or above 80 points, and so on.

2) The executed SQL statement is:
Copy codeThe Code is as follows:
Select ID, TestBase,
Case
When testBase> = 90 then 'A'
When testBase> = 80 then 'B'
When testBase> = 70 then 'C'
When testBase> = 60 then 'D'
Else 'E' end as testBaseLevel,
TestBeyond, testDate from Score

3) final execution result:

(4) Note:
1) write the end corresponding to the case.
2) end followed by alias (an object is equivalent to a field between case and end ))
(5) Comparison with the switch-syntax of C #
1) C #
Switch (variable)
{
Case constant 1: result 1; break;
Case constant 2: result 2; break;
Default: Default result; break;
}
2) SQL
I have written the SQL syntax above. Let's take a look at it.
(6) Corresponding if-else if syntax
1) C #
If (expression 1) {result 1 ;}
Else if (expression 2) {result 2 ;}
Else {default result ;}
2) SQL Server
Case
When expression 1 then display 1
When expression 2 then display 2
Else display by default
End
3) For example, if we use the f and m tags in the database when storing the gender, and now we want to use the male ID, the SQL statement is as follows:
Copy codeThe Code is as follows:
Select ID, Name, stuSex,
Case
When stuSex = 'M' then 'male'
When syuSex = 'F' then 'female'
Else 'others'
End as stuSexType,
StuDate from Student.

(7) practice cases:
1) execute this code in the database:
Copy codeThe Code is as follows:
Use Test
Go
Create table PracticeTest
(
Number varchar (10 ),
Amount int
)
Insert into PracticeTest (number, amount) values ('rk1 ', 10)
Insert into PracticeTest (number, amount) values ('rk2', 20)
Insert into PracticeTest (number, amount) values ('rd3',-30)
Insert into PracticeTest (number, amount) values ('rk4 ',-10)

2) The implementation result is as follows:

3) it can be seen that the select statement should have three fields and put the data greater than 0 into the income. The other field is 0 and the data smaller than 0 is put into the expenditure, the other is 0, and we will write the implemented SQL statement below:
Copy codeThe Code is as follows:
Select number as ticket number,
Case
When amount> 0 then amount
Else 0
End as income,
Case
When amount <0 then-amount
Else 0
End as expenditure
From PracticeTest

(8) exercises for one interview question:

1) Let's write the code to be executed below. You can create the database yourself or add a script below. You can execute the code for pharmaceuticals:

2) executed SQL statement:
Copy codeThe Code is as follows:
Create table Score
(
Student ID nvarchar (10 ),
Nvarchar (10 ),
Score int
)
Insert into Score values ('123', 'China', 87)
Insert into Score values ('123', 'mat', 79)
Insert into Score values ('20140901', 'English ', 95)
Insert into Score values ('20140901', 'China', 69)
Insert into Score values ('123', 'mat', 84)

3) Write functional SQL statements
Copy codeThe Code is as follows:
Select student ID, sum (
Case
When course = 'China' then score
Else 0
End) as language, sum (
Case
When course = 'mat' then score
Else 0
End) as mathematics, sum (
Case
When course = 'English 'then score
Else 0
End) as English
From score group by student ID

Believe in yourself, you are the next miracle!