数据库的分页方法总结

==============================================================================================

通用型分页（sql server ，access……）

declare @pageCount int,@pageNum int ,@pageRows int
set @pageNum =15
set @pageRows=20
set @pageCount =Ceiling( (select count(0) from table_name) *1.0/ @pageRows )
select top (@pageRows)* from table_name where I_Id not in (select top ((@pageNum-1)*@pageRows) table_name from Lm_wuzi)order by I_Id
select @pageCount

==============================================================================================

sql server 2005 专用（2005的函数）

create proc [dbo].[my_pages]
@pageSize int =20,
@pageNO int =1,
@pageCount int output,
@strWhere nvarchar(1000)=''
as
 set noCount on
begin
 if(@pageNo< 1 or @pageSize <1)
  begin
   raiserror ('分页数值无效',16,1)
   return -1
  end
  --返回总页数
  declare @allPage int
  declare @countSql nvarchar(1000),@sql nvarchar(4000)
  set @countSql = 'select @allPage = count(*) from table_name where 1=1' + @strWhere
  exec sp_executesql @countSql,N'@allPage int output',@allPage output
  set @pageCount =Ceiling( @allPage *1.0/ @pageRows  )

  set @sql='SELECT  *  from (select *,ROW_NUMBER() Over(order by I_Id) as rowNum from table_name where 1=1'+ @strWhere+') as myTable  WHERE rowNum between @PageSize*(@pageNo - 1)+1 and @pageSize * @pageNo'
  exec sp_executesql @sql ,N'@PageSize int,@pageNo int',@PageSize,@pageNo
 end
end

==============================================================================================

sql server 数据库通用型（内存表）

create proc [dbo].[MY_pages]
@pageSize int =20,
@pageNO int =1,
@pageCount int output,
@strWhere nvarchar(1000)
as
 set noCount on
begin
 if(@pageNo< 1 or @pageSize <1)
  begin
   raiserror ('分页数值无效',16,1)
   return -1
  end
 create table #temp(tid int identity(1,1),pid int)
 declare @sql nvarchar(2000)
 set @sql='insert into #temp (pid)  select I_id from table_name where 1=1'

 if(@strWhere <> '')
 set @sql = @sql +@strWhere
 
 exec sp_executesql @sql
 
 set @pageCount =Ceiling( @@rowcount * 1.0/ @pageSize)
 if (@pageNo >@pageCount)
 begin
  set @pageNo = @pageCount
 end 
 declare @startNo int,@endNo int
 set @startNo = @PageSize*(@pageNo - 1)+1
 set @endNo = @pageSize * @pageNo

 select * from table_name as b,#temp as c where b.I_id=c.pid and c.tid between @startNo and @endNo
 drop table #temp
end