SQL 分页

 

1：OffSet Rows  
declare @pagenum int;
declare @pagesize int;

set @pagesize=6;
set @pagenum=2;

select  BatchId,CreateDateTime from ECInventoryJson
order by BatchId,CreateDateTime
OFFSET  (@pagesize*(@pagenum-1)) rows
fetch next @pagesize rows only

--OFFSET 100 rows  偏移(跳过)100行
--FETCH NEXT 50 ROWS ONLY  (每页)只取50行

2：ROW_NUMBER() over(order by CreateDateTime)
declare @pagenum int;
declare @pagesize int;

set @pagesize=6;
set @pagenum=2;

select top (@pagesize) * from 
(
 --先给目标表设置一个Row_Number
  select ROW_NUMBER() over(order by CreateDateTime) num, * from ECInventoryJson
) a
where num>((@pagenum-1)* @pagesize)