子查询和高效分页

最新推荐文章于 2021-02-18 14:00:37 发布

weixin_30340353

最新推荐文章于 2021-02-18 14:00:37 发布

阅读量89

点赞数

原文链接：http://www.cnblogs.com/sumg/p/3649491.html

版权

select id from Classes where Name ='一期'
select* from Students where CId =(select id from Classes where Name ='一期')--子查询
select * from Students where CId in (select id from Classes where Name ='一期'

select * from Students where CId not in (select Id from Classes where Name ='一期')

select * from Students where exists (select * from Classes where Classes.Id=Students .CId) --主查询会根绝子查询的条件进行匹配

select * from Students where not exists (select * from Classes where Classes.Id=Students .CId)

--第一种分页方式，如果要取很多页之后的数据性能比较低
select top 10 * from Students where Id in (select top 10 Id from Students )--查出前十条数据

select top 10 * from Students where Id not in (select top 10 Id from Students )--查出第二页数据
--第二种分页比较高效，Row_Number()方法会给结果集编号
select * from (
select *,ROW_NUMBER()over(order by id) as rowsnumber from Students ) as t
where t.rowsnumber between 1 and 10

转载于:https://www.cnblogs.com/sumg/p/3649491.html