sqlserver 分页查询

sqlserver 中的分页查询

1. ROW_NUMBER() OVER() 排序1，使用 between and 方式

declare @pageIndex int
declare @pageSize int
set @pageIndex=0
set @pageSize=10

select * from (select ROW_NUMBER() over(partition by SameRow order by  Id ) as Row,* from (select *,1 as SameRow from (

-- 放入要需要分页的数据


)  t1)  t2)  o where o.Row between @pageIndex*@pageSize+1  and (@pageIndex+1)*@pageSize

2. ROW_NUMBER() OVER() 排序2，使用 BETWEEN AND方式

SELECT * FROM
            (
            SELECT 
            ROW_NUMBER()OVER(ORDER BY sn)Indexs
            ,* 
            FROM dbo.MES_PACKING_LABEL
            )Tab
WHERE Tab.Indexs BETWEEN 0 AND 10;

子查询

1. 子查询分页

declare @pageIndex int
declare @pageSize int
select top @pageSize*
from dbo.MES_PACKING_LABEL
where sn not in
(
 select top @pageSize*(@pageIndex-1) SN from dbo.MES_PACKING_LABEL order by sn
)
order by sn

2. 子查询分页

declare @pageIndex int
declare @pageSize int
select top @pageSize*
from dbo.MES_PACKING_LABEL
where sn >
(
 select isnull(max(sn),0)
 from 
  (
   select top @pageSize*(@pageIndex-1) sn from dbo.MES_PACKING_LABEL order by sn
  ) A
)
order by sn