Three ways to use sub-queries

Three ways to use subqueries:

1, sub-query as condition

1 "If you are following > < >= <= =! = <>, you must ensure that the subquery returns only a single value, and if you return a column of multiple rows, you need to specify the range using in, and if you return a row of multiple columns, an error

Eg: (Multiple rows and one column)

SELECT * from Student where ClassId on (select ClassId from grade)

2, the subquery acts as a result set (you can replace the table behind the from with the virtual result set)

According to the primary key Studentno sort, row_number () will have the line number, over () Specify the Sort field

Select *,row_number () over (order by Studentno) from Student

eg://The virtual result set requires the Set alias (temp) to generate the line number also to specify the alias (as ID)

SELECT * FROM (select *,row_number () over (order by STUDENTNO) as ID from Student) temp where id>0 and id<=5//pagination

3, sub-query as the value of the column

Select (select Studentname from Studnet where

Student.studentno=result.studentno), Studentresult from Result

Three ways to use sub-queries