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
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