Supplemental: Related queries, case functions in SQL Server

Related queries (in the same table)

Related queries are similar to subqueries, but are different from subqueries, and sub-conditions in subqueries can be isolated from data, but sub-conditions of related queries cannot be investigated. (Can be understood as a C # for the exhaustive method, the first for go one, the second for walk around, in the relevant query, the data in parentheses only a few, the outside query has all the data, each data into parentheses to compare the appropriate)

Format:

SELECT * FROM table name 1 as a where a. Column name relationship expression or logical operator

(

SELECT * FROM table name 1 as b where a. Related column name 1 = B. Related Column name 1

)

query principle for related queries: (in the same table) within parentheses, depending on the corresponding relationship of column names, if a the data equals b , the code inside the parentheses is executed

Case statement:

Similar to the switch () case usage in C #

Format:

Case a value or column name of an expression or not

When 1 Then '

When 2 Then '

CREATE TABLE Qiusai

(

Name nvarchar (10),

Score nvarchar (10)

)

INSERT into Qiusai values (' Bayern ', ' wins ')

INSERT into Qiusai values (' Wizards ', ' wins ')

INSERT into Qiusai values (' Lakers ', ' wins ')

INSERT into Qiusai values (' Bayern ', ' negative ')

INSERT into Qiusai values (' Bayern ', ' negative ')

INSERT into Qiusai values (' Wizards ', ' wins ')

SELECT * FROM Qiusai

Case Use of: (Add a Case represents an additional column, Case the usage and C # in Switch case ( similar to the usage)

Select Name,count (

(

Case Score

When ' win ' then ' 111 '

End

) as WINS, COUNT (

(

Case Score

When ' negative ' then ' 000 '

End

)) as negative

From Qiusai GROUP by name

Results after use

Supplemental: Related queries, case functions in SQL Server