1,case subquery Query connection
Copy Code code as follows:
SELECT * FROM Score
Create DATABASE Demo
Use Demo
CREATE TABLE [user]
(
[UId] int identity (1, 1) primary key,
[Name] varchar (50),
[Level] int--1 Ashes prawn rookie
)
insert into [user] (name, level) values (' Brother Sharp ', 1)
insert into [user] (name, level) values (' Xiao Yue Yue ', 2)
insert into [user] (name, level) values (' Sister Furong ', 3)
--case End single value judgment equivalent to switch case
The return value type must be the same after--then
Select [Name],
case [Level]
When 1 Then ' Ashes '
When 2 Then ' prawn '
When 3 Then ' rookie '
End as ' rank '
from [user]
Use MySchool
SELECT * FROM Score
--case end second usage, equivalent to multiple if statements
Select StudentID,
Case
When 中文版 >=90 then ' excellent '
When 中文版 >=80 and 中文版 <90 then ' Liang '
When 中文版 >=70 and 中文版 < Then '
When 中文版 >= and 中文版 < Then ' may '
Else ' poor '
End as ' results '
From score
ORDER BY 中文版
--The table has a B C three column, implemented in SQL statement: When column A is greater than column B, select column A or column B, select column B when column B is greater than column C, or select column C.
Select
Case
When a > B then a
Else b
End,
Case
When B > C then b
else C
End
From T
--Practice
CREATE TABLE Test
(
Number varchar (10),
Amount int
)
INSERT into test (number, amount) VALUES (' RK1 ', 10)
INSERT into test (number, amount) VALUES (' RK2 ', 20)
INSERT into test (number, amount) VALUES (' RK3 ',-30)
INSERT into test (number, amount) VALUES (' RK4 ',-10)
Select number,
Case
When amount > 0 then Amount
else 0
End as ' income ',
Case
When amount < 0 then abs (amount)
else 0
End as ' expense '
From Test
--The results are as follows
Copy Code code as follows:
--There is a table student0 to record students ' grades
Use Demo
CREATE TABLE student0 (name nvarchar (), subject nvarchar (), result int)
INSERT into Student0 VALUES (' John ', ' language ', 80)
INSERT into Student0 VALUES (' John ', ' Math ', 90)
INSERT into Student0 VALUES (' John ', ' Physics ', 85)
INSERT into Student0 VALUES (' Dick ', ' language ', 85)
INSERT into Student0 VALUES (' Dick ', ' math ', 92)
INSERT into Student0 VALUES (' Dick ', ' physics ', NULL)
SELECT * FROM Student0
Select [Name],
IsNull (sum (case subject
When ' language ' then result
End), 0) as ' language ',
IsNull (sum (case subject
When ' math ' then result
End), 0) as ' mathematics ',
IsNull (sum (case subject
When ' physical ' then result
End), 0) as ' physics '
From Student0
Group BY [name]
Copy Code code as follows:
--The subquery makes a query statement as a result set for use by other SQL statements, just as with ordinary tables,
-A query statement that is treated as a result set is called a subquery. Almost all the places where you can use a table can use subqueries instead.
Use MySchool
Select Sname from (SELECT * from student) as T
Select 1, (select SUM (中文版) from score) as ' and ', (select AVG (sAge) from student) as ' average age '
--Check all students in class one by one
SELECT * FROM student where Sclassid =
(select CId from class where CName = ' High One by one class ')
--Inquiry One by one high class 21 class all students
--The subquery returns more than one value. When the subquery follows the =,!=, <, <=, >, >=
--After the subquery follows the comparison operator, the subquery is required to return only one value
--If the subquery is a multiple-row subquery, the result set of such a subquery is actually a collection. You can use the IN keyword instead of the = number
SELECT * FROM student where Sclassid =
(select CId from class where cName in (' High class one by one ', ' High Class 21 '))
SELECT * FROM student where sclassid
(select CId from class where cName in (' High class one by one ', ' High Class 21 '))
--Check the results of Liu's closure
SELECT * FROM score where StudentID
(select SId from student where sname in (' Liu Bei ', ' Guan Yu ', ' Zhang Fei '))
--Delete Liu closure
Delete from score where StudentID
(select SId from student where sname in (' Liu Bei ', ' Guan Yu ', ' Zhang Fei '))
--Implementing paging
--A student who has recently enrolled
Select Top 3 * from student
ORDER BY sId Desc
--Search for the first student
Select Top 3 * from student
Where Sid not in (select Top 3 sId-student ORDER BY Sid DESC)
ORDER BY sId Desc
--The student in the query
Select Top 3 * from student
Where Sid not in (select Top 6 sId-student ORDER BY Sid DESC)
ORDER BY sId Desc
--Above is how SQL 2000 was implemented. After SQLServer2005, the Row_number function simplification is realized.
Pagination in--sql 2005
SELECT * FROM
(select Row_number () over (order by sId Desc) as num,* from student) as T
where num between 1 and 3
SELECT * FROM
(select Row_number () over (order by sId Desc) as num,* from student) as T
where num between 4 and 6
SELECT * FROM
(select Row_number () over (order by sId Desc) as num,* from student) as T
where num between 7 and 9
SELECT * FROM
(select Row_number () over (order by sId Desc) as num,* from student) as T
where num between 3 * (3-1) + 1 and 3 *3
--Table joins
--cross-connect cross Join
SELECT * FROM Student
Cross Join class
--Inner connection inner join...on ...
SELECT * FROM Student
INNER JOIN class on sclassid = CId
SELECT * FROM class
--Check the name, age and class of all students
Select Sname, SAge, CName, ssex from student
INNER JOIN class on sclassid = CId
where ssex = ' female '
--Inquire about the name, age and class of the student aged over
Select Sname, SAge, CName from class
Inner JOIN student on sclassid = CId
where SAge > 20
--External connection
--left Join...on ...
Select Sname, SAge, CName from class
