SQL Server分页

首先是一种通用的方法：利用top分页

declare @pageIndex int=1, @pageSize int=7
select * 
from (select top (@pageSize) * 
from(select top (@pageIndex*@pageSize) * 
from B_GoodsSKU 
order by NID asc) as temp_a order by NID desc )temp_b order by NID asc

这种方法要注意他的排序

第二种是在SQL Server2005版本以上才可以：利用ROW_NUMBER()

declare @pageIndex int=1, @pageSize int=7
select top (@pageSize) * 
from (select ROW_NUMBER() over(order by NID asc) as rownumber,*
from B_GoodsSKU) temp_row where rownumber>((@pageIndex-1)*@pageSize)

千万不要走我走过的坑：这等同于先全部查询出来，在分页，这样虽然能分页，但如果数据量大，SQL效率会缓慢

set statistics time on;
declare @pageIndex int=1, @pageSize int=7
if OBJECT_ID('tempdb..#t') is not null drop table #t
create table #t(
    tid int identity(1,1),
   sku varchar(100)
)
insert into #t(sku) select sku from B_GoodsSKU
select * from (select ROW_NUMBER()OVER(order by sku) as myrowid,sku from #t)as aa 
where myrowid>(@pageIndex-0)*@pageSize and myrowid<=(@pageIndex+1)*@pageSize