SQL Server Advanced Content case Syntax function Overview and use of _mssql

1. Use of Case functions
(1) using similar: 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) Hundred Transformation of quality education
1) as shown: We want to convert the displayed data to ABCDE, the rule is 90 points above show a,80 show B, and so on.

2 The SQL statement executed is:

Copy Code code 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 The final results of the execution are shown in the figure:

(4) Note:
1 write the case corresponding to the end.
2 End followed by alias (case and end is equivalent to a field (object))
(5) comparison with C # 's switch-syntax
1) C #
Switch (variable)
{
Case constant 1: result 1;break;
Case constant 2: result 2;break;
Defaults: default result;
}
2) SQL
SQL syntax I wrote on it, and I can look at it in comparison.
(6) The 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 default display
End
3 For example, if we have sex in the database is identified with the f,m, now we want to use the male and female identity, the SQL statement is as follows:

Copy Code code as follows:

Select Id,name,stusex,
Case
When stusex= ' m ' Then ' man '
When syusex= ' F ' then ' woman '
Else ' other '
End as Stusextype,
Studate from Student.

(7) Practice case:
1 execute this code in the database:

Copy Code code 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 (' RK3 ',-30)
Insert into Practicetest (Number,amount) VALUES (' RK4 ',-10)

2 The results of the implementation are as follows:

3 You can see that first select should have three fields, and the data is more than 0 of the revenue, then the other 0, and will be less than 0 of the expenditure inside, the other is 0, the following we write the implementation of the SQL statement:

Copy Code code as follows:

Select Number as Dan number,
Case
When amount>0 then amount
else 0
End as revenue,
Case
When Amount<0 Then-amount
else 0
End as expenditure
From Practicetest

(8) An interview question practice:

1) as shown in the figure: we write the following code, the database to build their own or I am in the following additional script, you can perform the pharmacy:

2 Execute the SQL statement:

Copy Code code as follows:

CREATE TABLE Score
(
School number nvarchar (10),
Course nvarchar (10),
Score int
)
INSERT into Score values (' 0001 ', ' language ', 87)
INSERT into Score values (' 0002 ', ' math ', 79)
INSERT into Score values (' 0003 ', ' English ', 95)
INSERT into Score values (' 0004 ', ' language ', 69)
INSERT into Score values (' 0005 ', ' math ', 84)

3 write the SQL statement that implements the function

Copy Code code as follows:

Select school Number, SUM (
Case
When course = ' language ' then results
else 0
End) as language, sum (
Case
When course = ' math ' then results
else 0
End) as mathematics, sum (
Case
When course = ' English ' then results
else 0
End) as English
From score Group by school number

Believe in yourself, you are the next miracle!