T-SQL备忘(4)：分页

set statistics io on 
set statistics time on 
--SQL Server 2012分页方式 
select * from Production.Product 
order by ProductID offset 20 row fetch next 10 rows only  
--SQL Server 05/08分页方式 
go 
with cte as  
( 
 select row_number() over(order by productid) as rowNum,* from production.product 
) 
select * from cte where rowNum>20 and rowNum<=30 
--or  
select * from (select row_number() over(order by productid) as rowNum,* from production.product) cte 
where rowNum>20 and rowNum<=30

--通用方式 
select top 10 * from Production.Product where ProductID not in 
( 
   select top 20 ProductID from Production.Product order by ProductID 
) order by ProductID 

select top 10 * from Production.Product where ProductID> 
( 
 select max(ProductID) from (select top 20 ProductID from Production.Product order by ProductID) as temp 
) order by ProductID