db-子查询

子查询关键字 = in exists in和exists是一样得，语法不同，大数据时候exists效率略高

1.哪些班级里面有学生

select * from class where cid in 

(select sClassId from Student)

2 哪些班级里面有学生 

select * from Class 
where exists
(select * from Student where cid=sClassId)

3 分页是典型的子查询，下面想当然的查询是错误的，因为执行顺序先执行from，再where，此时没有row

select *,ROW_NUMBER()  over(order by sid desc) as row from Student 
where row between 3 and 6

 下面的写法才正确，加入子查询获得row

select * from
(select *,ROW_NUMBER()  over(order by sid desc) as row from Student )as t1
where row between 3 and 6