SQL server--subquery

Briefly:

In the query statement contains another query statement, the contained query is called a subquery, the query containing the subquery is called the parent query.

In summary, a subquery is one or more query statements nested within a query statement.

Common sub-query categories:

One, independent sub-query

Features: Subquery statements can be run independently

Second, related sub-query

Select *   from where  = (Select  from where majorid=student.majorid)

Feature: A field in a parent query is referenced in a subquery, dependent on the parent query

The use of subqueries

Sub-query as a query condition

Description: When a query statement requires an external value as a condition, you can use a separate query to get this value before returning the value to the judgment of the condition.

Note 1: when using a subquery as a condition, the subquery's query results can return only one column of values, and if returning multiple columns will give an error:

NOTE 2: If the subquery returns the result of multiple rows in a single column, the "=" should be replaced with "in" after the query statement where, and the in keyword can receive the multiline result set. Use "=" to receive multiple lines of error:

Note 3: If the subquery is returned as a condition, the result set of multiple rows and multiple columns can be exists to receive the query results

Second, make the subquery as a result set

Select *  from  (Select  *   from where majorid=1where stugender=1

Note: To make a subquery as a result set, you must take an alias for the result set

Third, make a subquery as a column in the query statement

Select  Stuname, (select majorname from  where student.majorid=  from Student

Four, sub-query implementation paging

Mode 1:

-- pageindex=2  pagesize=5Select* from (select over (  Order by as num,* from donationdetail) tabwhere num>=6and<=

Mode 2:

--pageindex=3 pagesize=5Select Top 5 *  from(SelectRow_number () Over(Order  byDid asNum*  fromdonationdetail) TabwhereNum not inch(Select Top(3*5-5) Row_number () Over(Order  byDid asNum2 fromDonationdetail) 

Row_number () Over (Order by field):

Adds an uninterrupted line number to each row of the sorted result set, based on the specified field sort.

Note: If you use a query that generates line numbers as a result set, you must take an alias for the result set, and the resulting row number field takes an alias

SQL server--subquery