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