MSSQL分页取数的方法

在管理系统开发中,最常用的方法就是分页法

例: 获取表A(keyId 为标识列 第2页(每页显示20条数据)
declare @tableName varchar(50)
declare @pageSize int
declare @thisPage int

set @tableName =’A’
set @pageSize =20
set @thisPage =2


方法1 not in

select top @pageSize *
from @tableName
where (keyId not in (select top (@thisPage-1)*@pageSize keyId from @tableName ))


方法2 not exists

select top @pageSize * from @tableName AS a where not exists
(select * From (select top (@thisPage-1)*@pageSize * from @tableName order by id) b where b.id=a.id )
order by id


方法3 between

select * from @tableName where id between ((@thisPage-1)*@pageSize ,@thisPage*@pageSize)


方法4 通过max字段方法

select top @pageSize * from @tableName
where Id>(select max(Id) from
(select top m-1 Id from @tableName order by Id asc) tmp)
order by Id asc