Primary sub-query

One: Simple subquery

1: Example: Query the information of students larger than Lis Wen:

SELECT * FROM student where borndate>

(select Brondate from student where studentname= ' Lis Wen ')

Execution order: Executes the subquery in parentheses first, and then executes the parent query as a condition of the result returned by the subquery

Note: You can use comparison operators when the results of a subquery return a single value (for example: =,>,<,>=,<=)

What if a subquery returns multiple values (two or more) (filter range)?

2: Using the IN keyword

Example: Query Java Logic Course at least once a student list of exactly 60 points (WYSIWYG)

Select Studentname from Result where Studentno

(select Studentno from Result where subjectno=

(select Subjectno from subject where subjectname= ' Java logic ')

and studentresult>=60

)

The In keyword can be a parent query that matches a subquery that returns multiple single-column values

3:not in (not in the filtered range) keyword

Example: How to query a student list without taking a Java logic course

Select student name from student where Studentno not in

(select Studentno from result where subjectno=

(select Subjectno from subject where subjectname= ' Java Logic ')

)

4:exists and NOT EXISTS subqueries

exists keyword to retrieve the existence of data

Syntax: if exists (subquery)

Statement

Example: Check the most recent exam record of the OOP course, and if there is a score of 80, add 2 points per person or 5 points per person. Final result must not exceed 100 points

--Check the OOP last exam, if the score is more than 80 points plus 2 points,
--Otherwise, add 5 points, the final result shall not be greater than 100 points
print ' Original results of the students taking the exam '
Select Examdate, Studentno,studentresult from Result
where subjectid= (select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
if exists (
SELECT * FROM Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
and studentresult>80
)
Begin
Update Result set studentresult+=2 where subjectid=
(select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
and studentresult<=98
print ' Plus 2 score '
Select Examdate, Studentno,studentresult from Result
where subjectid= (select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
End
Else
Begin
Update Result set studentresult+=5 where subjectid=
(select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
print ' plus 5 score '
Select Examdate, Studentno,studentresult from Result
where subjectid= (select Subjectid from Subject where subjectname= ' oop ')
and examdate=
(select Max (examdate) from Result where subjectid=
(select Subjectid from Subject where subjectname= ' oop '))
End

Show:

5: Related sub-query:

The execution of the correlated subquery depends on the external query. In most cases, a table that references an external query in the WHERE clause of a subquery. Execution process:

(1) remove a tuple from the outer query and pass the value of the tuple-related column to the inner query.

(2) Execute the inner query to get the value of the subquery operation.

(3) an outer query obtains rows that satisfy a condition based on the result or result set returned by the subquery.

(4) then the outer query takes the next tuple and repeats the step 1-3until the outer tuple has finished processing.  

Example: querying The book information in the Books table that is larger than the price average of such books

Select  book name publishing house , price
  from books as a
   where  price   >
   (
    select avg ( price )
    from books as b
    where b.< Span style= "font-family: the song Body;" > class number =a.
  )
  go

Analysis: The outer query query is the price of the book (the condition is that the price of this book is greater than the average price of this kind of books), the inner query query is the average price of this kind of books (if the book is the same class of books), the table used is the same table, each with a different table alias, for example, the label of this kind of class is 2 will a. Type number =2 The internal query synchronization will B. Class number =2 (guaranteed that the book is the same class of books), the execution of the inner loop query to the average price of this kind of books, synchronous execution of the outer query query to filter out which book price is greater than the average price of this genre

6: Paged query: There are two methods

1: Double top Double order by method:

--Check the student information from 3rd to 5th in the Students ' table, haha
Select Top 3 * from Student
where Studentno not in
(select top 2 studentno from Student Order by Studentno)
ORDER BY Studentno

Parsing: Skip a few write a few, the inner query query is the record to skip, the outer query query is the number of records to be

2:row_number () over () function:

--Check the student information from 3rd to 5th in the students table, hehe
SELECT * FROM
(select *,row_number () over (order by Studentno) as myID from Student) as TMP
where myID between 3 and 5

Parse: Create a column of row ordinal value, named myID in order to allow the statement in the Where condition to recognize myID, write in the FROM statement (the key execution order: 1.from2.where3.select) and give the table an alias, Then use between ... and ... Make a paged query

Effect Completion diagram:

Primary sub-query