我要评论1.分页方案一：(利用not in和select top分页)
语句形式：
复制代码 代码如下:
select top 10 *
from testtable
where (id not in
(select top 20 id
from testtable
order by id))
order by id
select top 页大小 *
from testtable
where (id not in
(select top 页大小*页数 id
from 表
order by id))
order by id
2.分页方案二：(利用id大于多少和select top分页）
语句形式：
复制代码 代码如下:
select top 10 *
from testtable
where (id >
(select max(id)
from (select top 20 id
from testtable
order by id) as t))
order by id
select top 页大小 *
from testtable
where (id >
(select max(id)
from (select top 页大小*页数 id
from 表
order by id) as t))
order by id
3.分页方案三：(利用sql的游标存储过程分页)
复制代码 代码如下:
create procedure sqlpager
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第n页
@pagesize int --每页行数
as
set nocount on
declare @p1 int, --p1是游标的id
@rowcount int
exec sp_cursoropen @p1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
set nocount off
4.
复制代码 代码如下:
create procedure framworkpage
@tablename varchar(50), --表名
@fields varchar(5000) = '*', --字段名(全部字段为*)
@orderfield varchar(5000), --排序字段(必须!支持多字段)
@sqlwhere varchar(5000) = null,--条件语句(不用加where)
@pagesize int, --每页多少条记录
@pageindex int = 1 , --指定当前为第几页
@totalpage int output, --返回条数
@ordertype bit -- 设置排序类型,1 升序 0 值则降序
as
begin
declare @strorder varchar(400) -- 排序类型
begin tran --开始事务
declare @sql nvarchar(4000);
declare @totalrecord int;
--计算总记录数
if (@sqlwhere ='''' or @sqlwhere='' or @sqlwhere is null)
set @sql = 'select @totalrecord = count(*) from ' + @tablename
else
set @sql = 'select @totalrecord = count(*) from ' + @tablename + ' where ' + @sqlwhere
exec sp_executesql @sql,n'@totalrecord int output',@totalrecord output--计算总记录数
--计算总页数
select @totalpage=@totalrecord --ceiling((@totalrecord+0.0)/@pagesize)
if @ordertype = 0
begin
set @strorder = ' order by [' + @orderfield +'] desc'
--如果@ordertype是0，就执行降序，这句很重要！
end
else
begin
set @strorder = ' order by [' + @orderfield +'] asc'
end
if (@sqlwhere ='''' or @sqlwhere='' or @sqlwhere is null)
set @sql = 'select * from (select row_number() over( '+@strorder+' ) as rowid,' + @fields + ' from ' + @tablename
else
set @sql = 'select * from (select row_number() over( '+@strorder+' ) as rowid,' + @fields + ' from ' + @tablename + ' where ' + @sqlwhere
--处理页数超出范围情况
if @pageindex<=0
set @pageindex = 1
if @pageindex>@totalpage
set @pageindex = @totalpage
--处理开始点和结束点
declare @startrecord int
declare @endrecord int
set @startrecord = (@pageindex-1)*@pagesize + 1
set @endrecord = @startrecord + @pagesize - 1
if @ordertype = 0
begin
set @strorder = ' order by rowid desc'
--如果@ordertype是0，就执行降序，这句很重要！
end
else
begin
set @strorder = ' order by rowid asc'
end
--继续合成sql语句
set @sql = @sql + ') as ' + @tablename + ' where rowid between ' + convert(varchar(50),@startrecord) + ' and ' + convert(varchar(50),@endrecord) + ' '+@strorder
-- print @sql
exec(@sql)
---------------------------------------------------
if @@error <> 0
begin
rollback tran
return -1
end
else
begin
commit tran
return @totalrecord ---返回记录总数
end
end
通过上面这些东西的学习。我想一定可以找到一种满意的答案。。。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论