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

Source: Internet
Author: User

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!

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.