优点如下,简单,直接sql语句输入,高效,效率很高测试过了,不过最好是主键排序,还有少用视图 。
缺点也有,不支持复杂的sql语句,不能多字段排序,sql语句必须小于4000字符
/*
经测试,在 14483461 条记录中查询第 100000 页,每页 10 条记录按升序和降序第一次时间均为 0.47 秒,第二次时间均为 0.43 秒,测试语法如下:
exec GetRecordFromPage news,newsid,10,100000
news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*/
/*
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
作 者: 铁拳
邮 箱: sunjianhua_kki@sina.com
创建时间: 2004-07-04
修改时间: 2004-07-04
*/
CREATE PROCEDURE GetRecordFromPage1
@SQL varchar ( 8000 ), -- SQL语句
@PageSize int = 10 , -- 页尺寸
@PageIndex int = 1 , -- 页码
-- @strOut varchar(6000) output 输出处理完成的SQL语句
@RecordCount int = 0 output -- 输出总记录数
AS
declare @strSQL varchar ( 8000 ) -- 最后获取的SQL语句
declare @strTmp varchar ( 8000 ) -- 临时变量
declare @strOrder varchar ( 500 ) -- 排序类型
declare @tblName varchar ( 255 ) -- 表名
declare @fldName varchar ( 255 ) -- 字段名
declare @strWhere varchar ( 8000 ) -- 查询条件 (注意: 不要加 where)
declare @tmpOrder varchar ( 255 ) -- 排序字符串临时变量
declare @strFilds varchar ( 8000 ) -- 需要显示的列
declare @intFilds int -- 显示的列所在位置
declare @intOrder int -- 排序字符串位置
declare @intSQL int -- SQL语句长度
declare @intWhere int -- where字符串位置
declare @intTable int -- 表名称位置
declare @strRsSql nvarchar ( 4000 ) -- 统计总记录数sql语句
set @intOrder = CharIndex ( ' order by ' , @SQL )
set @intSQL = Len ( @SQL )
set @intFilds = CharIndex ( ' select ' , @SQL )
set @strFilds = SubString ( @SQL , @intFilds + 7 , @intSQL - @intFilds - 1 )
set @strFilds = SubString ( @strFilds , 1 , CharIndex ( ' from ' , @strFilds ) - 1 )
set @tblName = SubString ( @SQL , CharIndex ( ' from ' , @SQL ) + 5 , @intSQL - CharIndex ( ' from ' , @SQL ) + 5 )
set @intTable = CharIndex ( ' ' , @tblName )
if @intTable > 0
begin
set @tblName = SubString ( @tblName , 1 , @intTable - 1 )
end
set @intWhere = CharIndex ( ' where ' , @SQL )
if @intWhere > 0
begin
if @intOrder > 0
begin
set @strWhere = SubString ( @SQL , @intWhere + 6 , @intOrder - @intWhere - 7 )
end
else
begin
set @strWhere = SubString ( @SQL , @intWhere + 6 , @intSQL - @intWhere - 5 )
end
end
else
begin
set @strWhere = ''
end
if @intOrder > 0
begin
set @strOrder = ' ' + SubString ( @SQL , @intOrder , @intSQL - @intOrder + 1 )
set @tmpOrder = SubString ( @strOrder , 11 , Len ( @strOrder ) - 10 )
if CharIndex ( ' ' , @tmpOrder ) > 0
begin
set @fldName = SubString ( @tmpOrder , 1 , CharIndex ( ' ' , @tmpOrder ) - 1 )
end
else
begin
set @fldName = @tmpOrder
end
if CharIndex ( ' desc ' , @SQL ) > 0
begin
set @strTmp = ' <(select min '
end
else
begin
set @strTmp = ' >(select max '
end
end
else
begin
set @fldName = ' ID '
set @strTmp = ' >(select max '
set @strOrder = ' order by [ ' + @fldName + ' ] asc '
end
set @strSQL = ' select top ' + LTrim ( str ( @PageSize )) + ' ' + @strFilds + ' from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ '
+ @fldName + ' ]) from (select top ' + LTrim ( str (( @PageIndex - 1 ) * @PageSize )) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] ' + @strOrder + ' ) as tblTmp) '
+ @strOrder
if @strWhere != ''
set @strSQL = ' select top ' + LTrim ( str ( @PageSize )) + ' ' + @strFilds + ' from [ '
+ @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ ' + @fldName + ' ]) from (select top ' + LTrim ( str (( @PageIndex - 1 ) * @PageSize )) + ' [ '
+ @fldName + ' ] from [ ' + @tblName + ' ] where ' + @strWhere + ' '
+ @strOrder + ' ) as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ( ' + @strWhere + ' ) '
set @strSQL = ' select top ' + LTrim ( str ( @PageSize )) + ' ' + @strFilds + ' from [ '
+ @tblName + ' ] ' + @strTmp + ' ' + @strOrder
end
exec ( @strSQL )
-- ---统计总记录数
if @strWhere != ''
begin
set @strRsSql = ' select @RecordCount=count( ' + @fldName + ' ) from [ ' + @tblName
+ ' ] where ( ' + @strWhere + ' ) '
end
else
begin
set @strRsSql = ' select @RecordCount=count( ' + @fldName + ' ) from [ ' + @tblName
+ ' ] '
end
EXEC sp_executesql @strRsSql ,N ' @RecordCount int out ' , @RecordCount out
-- ---统计结束
-- set @strOut=@strSQL
GO