-写法1,not in/top select top 50 * from pagetest where id not in (select top 9900 id from pagetest order by id) order by id --写法2,not exists select top 50 * from pagetest where not exists (select 1 from (select top 9900 id from pagetest order by id)a where a.id=pagetest.id) order by id --写法3,max/top select top 50 * from pagetest where id>(select max(id) from (select top 9900 id from pagetest order by id)a) order by id --写法4,row_number() select top 50 * from (select row_number()over(order by id)rownumber,* from pagetest)a where rownumber>9900 select * from (select row_number()over(order by id)rownumber,* from pagetest)a where rownumber>9900 and rownumber<9951 select * from (select row_number()over(order by id)rownumber,* from pagetest)a where rownumber between 9901 and 9950 --写法5,在csdn上一帖子看到的,row_number() 变体,不基于已有字段产生记录序号,先按条件筛选以及排好序,再在结果集上给一常量列用于产生记录序号 select * from ( select row_number()over(order by tempColumn)rownumber,* from (select top 9950 tempColumn=0,* from pagetest where 1=1 order by id)a )b where rownumber>9900
几种典型的分页sql,下面例子是每页50条,198*50=9900,取第199页数据。
最新推荐文章于 2020-05-18 17:33:04 发布