黑马视频-子查询

子查询：独立子查询相关子查询

select * from (select col1,col2 from Tbl) as Tb

1、一个查询结果作为另一个查询的数据源 ,必须将子查询数据源别名 as Tbs

select * from (select

fname ,fage,fgender

from

MyStudent

where fage between 18 and 25 and fgender='女') as Tbs

where fname like '%赵'

select 最高分= select max(score) from Tbs,

最低分=select min(score) from Tbs,

平均分=select avg(score) from Tbs

select

from

TblStudent

where

tsclassid=(select tsclassid form TblClass where className ='高二二班')

4、子查询只能返回单个值

select * from Tbls where classId in (select classId from Tbs where classId='4' or classId='5')

5、exists中包含的查询查到了结果则exists返回true，否则为false

if(exists(select * from Tbl where 1<>1))
begin
print '有结果'
end
else
begin
print '无结果'
end

select * from Tbl where exists(

select * from Tbl1 where name='高二二班' and Tbl.classId=Tbl1.classId

)

select * ,row_number() over (order by fid desc) as number from tb

20页，每页5条

select * from (

select *,row_number() over (order by fid dsc ) as number from Tb

)

as TbNew where tbNew.number between 19*5+1 and 20*5

row_number() 行号

select * ,count(*) from Tbl （错误）

select * ,count(*) over () as 总条数 from Tbl