求数据量大，高效的分页方法。带查询参数的。
解决方案 »
- 问个日期的问题
- sql2000 max(字符串)问题？
- 问一个很简单的问题
- 求教:怎么在asp.net中使用aspose.excel组件
- asp.net网站问题
- 那个第三方控件最好用？
- 我的vs.net只能安装到这里,你看怪不怪!!!
- 如何在点击DataGrid的删除记录的列的时候弹出一个确认对话框？
- Could not create an environment: OCIEnvCreate returned -1.
- 问一个treeview比较简单的问题
- 对路径“E:\我的文档\Visual Studio 2005\WebSites\Search\crawled\segments”的访问被拒绝
- DATAGRID中显示 的oracle number(10,4)类型数据，如何把小数点后面的 0 去掉？
新增 PagingButtonClass 与 PagingButtonStyle 属性，可以单独为分页导航按钮（数字页索引、上页、下页、首页和尾页）设置CSS样式；
新增 FirstLastButtonClass 与 FirstLastButtonStyle 属性，可以单独为首页和尾页分页导航按钮设置CSS样式，如果该属性未设置，但指定了PagingButtonClass 与 PagingButtonStyle 属性的值，则首页和尾页按钮样式将使用 PagingButtonClass 与 PagingButtonStyle 属性中指定的样式；
新增 NextPrevButtonClass 与 NextPrevButtonStyle 属性，可以单独为上页和下页分页导航按钮设置CSS样式，如果该属性未设置，但指定了PagingButtonClass 与 PagingButtonStyle 属性的值，则上页和下页按钮样式将使用 PagingButtonClass 与 PagingButtonStyle 属性中指定的样式；
新增 MoreButtonClass 与 MoreButtonStyle 属性，可以单独为更多页（...）分页导航按钮设置CSS样式，如果该属性未设置，但指定了PagingButtonClass 与 PagingButtonStyle 属性的值，则上页和下页按钮样式将使用 PagingButtonClass 与 PagingButtonStyle 属性中指定的样式；
新增属性 ShowMoreButtons ，可以指定是否显示更多页按钮；
新增属性 CurrentPageButtonPosition ，可设置在每次分页后，当前页数字索引在所有的数字页索引中的显示位置，该属性值是一个PagingButtonPosition枚举，对应的值及说明如下：
Beginning：当前页数字索引总是显示在所有数字页索引的最前面；
End：当前页数字索引总是显示在所有数字页索引的最后面；
Center：当前页数字索引总是显示在所有数字页索引的中间；
Fixed：默认值，固定不变；
控件的CssClass属性仅应用于控件的窗口元素（div），将不再应用于下属分页元素；
废止属性CenterCurrentPageButton，可以用CurrentPageButtonPosition属性取代；
修改CurrentPageIndexn属性，允许在程序中任何地方以编程方式设置CurrentPageIndex的值来动态指定当前页，直接设置该属性的值时将同时引发PageChanging和PageChanged 事件，实现和点击分页按钮一样的分页功能；
修正了7.1版中设置SubmitButtonImageUrl属性后，Postback回发分页方式情况下点击数字页索引按钮不引发分页事件的bug；
修正了使用Url分页时，如果页面上没有服务器端form控件时无法注册客户端脚本的bug，从7.2版起如果使用Url分页并且ShowPageIndexBox属性没有设置为Never时， AspNetPager控件必须放在<form runat="server">与</form>标记之间，若使用Url分页并且ShowPageIndexBox为Never时，页面上可以不使用服务器端form控件。
http://blog.csdn.net/azheng270/archive/2008/10/29/3176253.aspx
/*--用存儲過程實現的分頁程式
顯示指定表、視圖、查詢結果的第X頁
對於表中主鍵或標識列的情況,直接從原表取數查詢，其他情況使用臨時表的方法
如果視圖或查詢結果中有主鍵,不推薦此方法--鄒建 2003.09(引用請保留此資訊)--*//**//*--調用示例select top 20 * from product_schedule where brand='oem'
exec p_show 'product_schedule',10,2,'','schedule_id'select top 20 * from product_schedule where brand='oem' and 1=2
exec p_show 'select schedule_id, priority, merchandising_team, merchandizer, customer,brand from product_schedule where brand=''oem'' and 1=1 ',
10,2,' priority, merchandising_team, merchandizer, customer','schedule_id'select * from uv_product_report order by year,month,day,product_dest,brand,styling,qty
exec p_show 'uv_product_report',1500,4,'year,month,day,product_dest,brand,styling,qty','year,month,day,product_dest,brand,styling,qty'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GOCREATE Proc dbo. p_show
@QueryStr nvarchar(4000), --表名、視圖名、查詢語句
@PageSize int=10, --每頁的大小(行數)
@PageCurrent int=1, --要顯示的頁
@FdShow nvarchar (4000)='',--要顯示的欄位列表,如果查詢結果有標識欄位,需要指定此值,且不包含標識欄位
@FdOrder nvarchar (1000)='' --排序欄位列表
as
declare @FdName nvarchar(250) --表中的主鍵或表、臨時表中的標識列名
,@Id1 varchar(20),@Id2 varchar(20) --開始和結束的記錄號
,@Obj_ID int --對象ID
--表中有複合主鍵的處理
declare @strfd nvarchar(2000) --複合主鍵列表
,@strjoin nvarchar(4000) --連接欄位
,@strwhere nvarchar(2000) --查詢條件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果顯示第一頁，可以直接用top來完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end--如果是表,則檢查表中是否有標識更或主鍵
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中無標識列,則檢查表中是否有主鍵
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中無主鍵,則用臨時表處理 select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --檢查表中的主鍵是否為複合主鍵
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/**//*--使用標識列或主鍵為單一欄位的處理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return/**//*--表中有複合主鍵的處理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return/**//*--用臨時表處理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
如果是sqlserver2005可以考虑使用ROW_NUMBER，比较简单，示例如下：
select *
from
(
select row_number() over(order by FieldA) as RowNum, TableA.*
from TableA
) t
where
t.RowNum between 10 and 20
http://www.cnblogs.com/yangbin1005/archive/2008/04/26/1156980.html
http://www.cnblogs.com/evernory/archive/2006/11/15/561254.html
create Proc [dbo].[GetRS]
@QueryStr nvarchar(300),--表名、视图名、查询语句
@PageSize int=10,--每页的大小(行数)
@PageCurrent int=1,--要显示的页
@FdShow nvarchar (100)='',--要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (100)='',--排序字段列表
@WhereStr nvarchar (200)='', --内容是' id=3 and model_no like '%24%' and '
@RSCount int=0 output
as
set @FdShow=' '+@FdShow+' '
set @FdOrder= ' '+@FdOrder+' '
set @WhereStr= ' '+@WhereStr+' 'declare @FdName nvarchar(250)--表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20)--开始和结束的记录号
,@Obj_ID int --对象ID
,@Temp nvarchar(300) --临时语句
,@strParam nvarchar(100) --临时参数declare @strfd nvarchar(2000)--复合主键列表
,@strjoin nvarchar(4000)--连接字段
,@strwhere nvarchar(2000)--查询条件
--检查输入参数
set @QueryStr=ltrim(rtrim(@QueryStr))
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--输出总记录数
SET @Temp= 'select @RSCount=count(*) FROM ' + @QueryStr+' '+@WhereStr
SET @strParam = N'@RSCount INT OUT'
EXECUTE sp_executeSQL @Temp,@strParam,@RSCount out
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@WhereStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0--如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp--如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))if @@rowcount>1--检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
if len(@WhereStr)>10
begin
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+@WhereStr+' and '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@WhereStr+@FdOrder
+')'+@FdOrder
)
return
end
else
begin
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
end
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 1, ----每页显示的记录个数
@page int = 10, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@fldSort nvarchar(200) = null, ----排序字段列表或条件
@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(1000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则BDeclare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts --取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize /**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount --/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小 set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize --//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+')'
+' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+')' +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
+' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
/*
***************************************************************
** 分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where
7.Group :Group语句,不带Group By
测试(zhanglei) : Exec Pager_Proc 'clientbase','no','no desc',0,30,'*','state<>4',''
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
Select @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
)--得到此表的总的记录数
Declare @sqlcount varchar(500)
set @sqlcount=N''
set @sqlcount='select count(1) from '+@Tables+' '
Exec (@sqlcount) 咱也发一个
http://www.webdiyer.com/AspNetPager/demo/search/default.aspx 这个分页控件是哪个版本的？麻烦用过的朋友介绍一下，谢谢了！
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@RecordCount int OUTPUT, --总记录数
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @RecordCount = @PageCount
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
2000中分页使用 COUNT(*) ,临时表 ,或者 TOP 关键字 效率大概是n^2的 下降
2005 使用ROW_NUMBER() 使用ROW_NUMBER() 是非常快速的
类型的数据 LIKE 关键字 已经不再搞笑了
使用 全文索引 加 ROW_NUMBER()
或者 全部临时表 T<guid> 类似的表来 建立分页 ,而且分页中 分为 顺序访问分页 还是不规则访问 分页采用不同的方法
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + 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 " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
end
if @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
exec (@strSQL)
GO
------------------------------------
--用途：分页存储过程(对有主键的表效率极高)
--说明：
------------------------------------CREATE PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsReCount != 0
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhereexec (@strSQL)GO
CREATE PROCEDURE [dbo].[getPage]
@pageSize int, --页尺寸
@currentPage int, --当前页
@tableFields varchar(2000), --返回的字段
@tableName varchar(200), --表名
@orderString varchar(1000), --排序字符串
@whereString varchar(1000), --条件字符串
@IsReCount bit = 0 -- 返回记录总数, 非 0 值则返回
AS
BEGIN if @currentPage < 1 set @currentPage = 1 DECLARE @strSql varchar(2000)
DECLARE @strOrder varchar(2000)
DECLARE @strWhere varchar(2000) set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)), 'order By ', ' ')
if @strOrder != '' set @strOrder = ' order By ' + @strOrder set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)), 'where ', ' ')
if @strWhere != ''
set @strWhere = ' where ' + @strWhere
else
set @strWhere = ' where 1=1 ' if @pageSize = 0
set @strSql = 'select ' + @tableFields + ' from ' + @tableName + @strWhere + @strOrder
else
if @currentPage = 1
set @strSql = 'select top( ' + Str(@pageSize) + ') ' + @tableFields + ' from ' + @tableName + @strWhere + @strOrder
else
begin
set @strSql = 'select top( ' + Str(@pageSize) + ') * from (select top( ' + Str(@pageSize * @currentPage) + ') ' + @tableFields + ', ROW_NUMBER() OVER ( '
set @strSql = @strSql + @strOrder
set @strSql = @strSql + ') As RowNumber From ' + @tableName
set @strSql = @strSql + @strWhere
set @strSql = @strSql + ') as t where t.RowNumber > ' + Str(@pageSize * (@currentPage - 1))
set @strSql = @strSql + @strOrder
end if @IsReCount != 0
set @strSql = 'select count(*) as Total from [' + @tableName + '] ' + @strWhere exec(@strSql)
END
ALTER PROCEDURE dbo.sp_GetTask
(
@CustomerCode varchar(50),
@CustomerName varchar(50),
@StartTime varchar(50),
@EndTime varchar(50),
@Employee int,
@pagesize int,
@pageindex int,
@docount bit
)
AS
begin
declare @sql nvarchar(4000),@strsql nvarchar(4000)
set @sql=' '
if(Ltrim(@CustomerCode)<>'' and @CustomerCode is not null)
begin
set @CustomerCode='%'+@CustomerCode+'%'
set @sql=@sql+' tt.CustomerCode like @CustomerCode and '
end
if(Ltrim(@CustomerName)<>'' and @CustomerName is not null)
begin
set @CustomerName='%'+@CustomerName+'%'
set @sql=@sql+' tc.CustomerName like @CustomerName and '
end
if(@StartTime<>'' or @EndTime<>'')--通话时间
begin
set @sql=@sql+' (tt.NeedCallbackTime between @StartTime and DATEADD(day,1,@EndTime)) and '
end
if(@Employee>0)
begin
set @sql=@sql+'tt.Employee=@Employee and '
end
if(@sql<>'')
set @sql=' where tt.urgentFlg=1 and ' +left(@sql,len(@sql)-4)
else
set @sql=' 'set @strsql=' if(@docount=1)
select Count(*) from TR_C_Task tt
inner join TR_P_Customer tc on tc.CustomerCode=tt.CustomerCode
inner join TR_C_Campaign tm on tm.CampaignCode=tt.CampaignCode '+ @sql +
' else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid)
select tt.TaskKey from TR_C_Task tt
inner join TR_P_Customer tc on tc.CustomerCode=tt.CustomerCode
inner join TR_C_Campaign tm on tm.CampaignCode=tt.CampaignCode'+ @sql + ' order by tt.NeedCallbackTime asc
select tt.TaskKey,tc.Source,tc.CustomerCode,tc.CustomerName,tm.CampaignName,dbo.GetEndCode(tt.TaskCode,tc.CustomerKey) EndCode,
dbo.GetAspiration(tt.TaskCode,tc.CustomerKey) Aspiration,tt.NeedCallbackTime,tc.LastCallTime,tc.Description,tc.CustomerKey
from TR_C_Task tt
inner join TR_P_Customer tc on tc.CustomerCode=tt.CustomerCode
inner join TR_C_Campaign tm on tm.CampaignCode=tt.CampaignCode
inner join @indextable t on t.nid=tt.TaskKey
where t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end '
exec sp_executesql @strsql,N'
@CustomerCode varchar(50),
@CustomerName varchar(50),
@StartTime varchar(50),
@EndTime varchar(50),
@Employee int,
@pagesize int,
@pageindex int,
@docount bit
',
@CustomerCode ,
@CustomerName ,
@StartTime ,
@EndTime ,
@Employee ,
@pagesize ,
@pageindex ,
@docount end
楼主请删除我在41楼的回复删除，我有做修改的，谢谢
楼主请删除我在41楼的回复删除，我有做修改的，谢谢
楼主请删除我在41楼的回复删除，我有做修改的，谢谢
楼主请删除我在41楼的回复删除，我有做修改的，谢谢Create PROCEDURE [dbo].[UP_SplitDataSet]
@iRowCount int output, -- 返回记录总数
@iPagesCount INT OUTPUT, --返回记录页数
@tblName nvarchar(1000), -- 表名
@SelectFieldName nvarchar(4000), -- 要显示的字段名(不要加select)
@strWhere nvarchar(4000), -- 查询条件(注意: 不要加 where)
@OrderFieldName nvarchar(255), -- 排序索引字段名
@PageSize int, -- 页大小
@PageIndex int = 1, -- 页码
@OrderType bit = 0 -- 设置排序类型, 非 0 值则降序
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(4000) -- 临时变量
declare @strOrder nvarchar(400) -- 排序类型
declare @strRowCount nvarchar(4000) -- 用于查询记录总数的语句 set @OrderFieldName=ltrim(rtrim(@OrderFieldName))if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = ' >(select max '
set @strOrder = ' order by ' + @OrderFieldName +' asc'
endset @strSQL = N'select top ' + str(@PageSize) + ' ' + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ ' ' + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)'
+ @strOrderif @strWhere != ''
set @strSQL = N'select top ' + str(@PageSize) + ' ' + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ ' ' + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere set @strSQL = N'select top ' + str(@PageSize) + @SelectFieldName + ' from '
+ @tblName + @strTmp + ' ' + @strOrder
end EXEC(@strSQL)
--PRINT @STRSQLif @strWhere!=''
begin
set @strRowCount = N'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere
end
else
begin
set @strRowCount = N'select @iRowCount=count(*) from ' + @tblName
end
--return @iRowCount
EXEC sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount outIF @iRowCount % @PageSize = 0
SET @iPagesCount = @iRowCount / @PageSize
ELSE
SET @iPagesCount = CEILING(@iRowCount / @PageSize) + 1
@tableName varchar(20),//表名
@orderField varchar(20),//排序字段
@pageSize int,//每页多少条数据
@currentPage int//当前第几页
as
begin
declare @sql nvarchar(max);
set @sql = 'select * from (select ((((row_number() over (order by @orderField))-1)/'+convert(varchar,@pageSize)+')+1) rowID, * from '+@tableName+') as a where rowID = '+convert(varchar,@currentPage);
exec sp_executesql @sql;
end;自己一直在用的存储过程,比较简单,希望你能有用
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--名称：分页存储过程
--使用示例 EXEC sp_PageIndex '*',' FROM StuSources ',2,10
--注意
--目前还没有对输入的参数进行严格的验证
--默认为输入都是合法有效的ALTER PROC sp_PageIndex
@sqlSelect varchar(800) --SELECT 后面 FROM 前面 的 字段 不用包含SELECT
,@sqlFrom varchar(800) --FROM 后面 的 字段 包含FROM
,@countPerPage int -- 每页数据行数
,@toPage int --要转到的页码ASBEGIN
-- 根据每页数据行数 和 要转到的页码 得到 数据起止点
Declare @start int
Declare @end intset @end = @countPerPage * @toPage
set @start = @countPerPage * (@toPage - 1) + 1
-- 临时表名称 可随机命名
Declare @tmpTable varchar(10)
SET @tmpTable ='#tmp'Declare @sqlStr varchar(800)
-- 创建数据源到临时表
SELECT @sqlStr = 'SELECT Identity(int,1,1) AS RowIndex,'
SELECT @sqlStr = @sqlStr + rtrim(@sqlSelect) + ' INTO '+ @tmpTable
SELECT @sqlStr = @sqlStr + rtrim(@sqlFrom)
-- 查询临时表 得到所需要的数据
SELECT @sqlStr = @sqlStr + ' '+'SELECT '+ rtrim(@sqlSelect) +' FROM ' + @tmpTable
SELECT @sqlStr = @sqlStr + ' WHERE RowIndex BETWEEN ' + Convert(char,@start) + " AND " + Convert(char,@end)
-- 删除临时表
SELECT @sqlStr = @sqlStr + ' '+'DROP TABLE '+@tmpTable
EXEC (@sqlStr)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
(@pagesize int, --页面大小，如每页存储20条记录
@pageindex int --当前页码)
as set nocount on begin declare @indextable table(id int identity(1,1),nid int) --定义表变量
declare @PageLowerBound int --定义此页的底码
declare @PageUpperBound int --定义此页的顶码 set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid
and t.id>@PageLowerBound and t.id <=@PageUpperBound order by t.id end set nocount off 文章中的点评： 以上存储过程运用了SQL SERVER的最新技术――表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然，在这个过程中，您也可以把其中的表变量写成临时 表：CREATE TABLE #Temp。但很明显，在SQL SERVER中，用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时，感觉非常的不错，速度也比原来的ADO的好。但后来，我又发现了比此方 法更好的方法。 从感觉上讲，效率不是太高。
2. not in 的方法：
从publish 表中取出第 n 条到第 m 条的记录： SELECT TOP m-n+1 * FROM publish WHERE (id NOT IN (SELECT TOP n-1 id FROM publish)) id 为publish 表的关键字 文章中的点评： 我当时看到这篇文章的时候，真的是精神为之一振，觉得思路非常得好。等到后来，我在作办公自动化系统（ASP.NET+ C#＋SQL SERVER）的时候，忽然想起了这篇文章，我想如果把这个语句改造一下，这就可能是一个非常好的分页存储过程于是我就满网上找这篇文章，没想到，文章还 没找到，却找到了一篇根据此语句写的一个分页存储过程，这个存储过程也是目前较为流行的一种分页存储过程。 使用了 not in 而 not in 是无法使用索引的，所以从效率上讲还是差了一点。
3. max 的方法： select top 页大小 * from table1 where id>
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T)
order by id 文章中的点评： 我们知道，几乎任何字段，我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值，所以如果这个字段不重复，那么就可以利用这些不 重复的字段的max或min作为分水岭，使其成为分页算法中分开每页的参照物。在这里，我们可以用操作符“>”或“ <”号来完成这个使命，使查询语句符合SARG形式。如：
Select top 10 * from table1 where id>200 这个就高高效了一点。但是不清楚 max的工作原理，不知道它的性能如何。 心得： 1、追求高效的翻页算法 —— 定位法。 declare @pageSize int --返回一页的记录数
declare @CurPage int --页号（第几页）0：第一页；-1最后一页。 declare @Count int
declare @id int set @pageSize=10
set @CurPage =1 --定位
if @CurPage = -1
begin
--最后一页
set rowcount @pageSize
select @id=newsID from newsTemp order by newsID
end if @CurPage > 0
begin
set @Count = @pageSize * (@CurPage -1) + 1
set rowcount @Count
select @id=newsID from newsTemp order by newsID desc
end --返回记录
set rowcount @pageSize
select * from newsTemp where newsID <=@id order by newsID desc set rowcount 0
思路：就是上面的算法的延续，就是说呢避免使用 not in 和 max 的方法。也就是这个思路：Select top 10 * from table1 where id>200，定位 —— 就是说要找到“临界点”，分页的临界点。找到了之后剩下的事情就好办了。 缺点：单字段排序、排序字段的值不能重复（不是绝对不能重复，可以有少量的重复）。
2、通用法 —— 颠颠倒倒法 有的时候“定位法”的缺点是不可以接受的，但是没有关系，可以用这个的。 select * from table where id in
(
select top 10 ID from
(
select top 20 ID,addedDate from table
order by addedDate desc
) as aa order by addedDate
)
order by addedDate desc ID 是主键，addedDate 是排序字段。 缺点：必须有主键。
2. <html xmlns="http://www.w3.org/1999/xhtml">
3. <head>
4. <meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
5. <title> JavaScript: showPages v1.0 [by Lapuasi.com]</title>
6. <script language="JavaScript">
7. <!--
8. /*
9.
10. showPages v1.1
11. =================================
12.
13. Infomation
14. ----------------------
15. Author : Lapuasi
16. E-Mail : [email protected]
17. Web : http://www.lapuasi.com
18. Date : 2005-11-17
19.
20.
21. Example
22. ----------------------
23. var pg = new showPages('pg');
24. pg.pageCount = 12; //定义总页数(必要)
25. pg.argName = 'p'; //定义参数名(可选,缺省为page)
26. pg.printHtml(); //显示页数
27.
28.
29. Supported in Internet Explorer, Mozilla Firefox
30. */
31.
32. function showPages(name) { //初始化属性
33. this.name = name; //对象名称
34. this.page = 1; //当前页数
35. this.pageCount = 1; //总页数
36. this.argName = 'page'; //参数名
37. this.showTimes = 1; //打印次数
38. }
39.
40. showPages.prototype.getPage = function(){ //丛url获得当前页数,如果变量重复只获取最后一个
41. var args = location.search;
42. var reg = new RegExp('[\?&]?' + this.argName + '=([^&]*)[&$]?', 'gi');
43. var chk = args.match(reg);
44. this.page = RegExp.$1;
45. }
46. showPages.prototype.checkPages = function(){ //进行当前页数和总页数的验证
47. if (isNaN(parseInt(this.page))) this.page = 1;
48. if (isNaN(parseInt(this.pageCount))) this.pageCount = 1;
49. if (this.page < 1) this.page = 1;
50. if (this.pageCount < 1) this.pageCount = 1;
51. if (this.page > this.pageCount) this.page = this.pageCount;
52. this.page = parseInt(this.page);
53. this.pageCount = parseInt(this.pageCount);
54. }
55. showPages.prototype.createHtml = function(mode){ //生成html代码
56. var strHtml = '', prevPage = this.page - 1, nextPage = this.page + 1;
57. if (mode == '' || typeof(mode) == 'undefined') mode = 0;
58. switch (mode) {
59. case 0 : //模式1 (页数,首页,前页,后页,尾页)
60. strHtml += '<span class="count">Pages: ' + this.page + ' / ' + this.pageCount + '</span>';
61. strHtml += '<span class="number">';
62. if (prevPage < 1) {
63. strHtml += '<span title="First Page">«</span>';
64. strHtml += '<span title="Prev Page">‹</span>';
65. } else {
66. strHtml += '<span title="First Page"><a href="javascript:' + this.name + '.toPage(1);">«</a></span>';
67. strHtml += '<span title="Prev Page"><a href="javascript:' + this.name + '.toPage(' + prevPage + ');">‹</a></span>';
68. }
69. for (var i = 1; i <= this.pageCount; i++) {
70. if (i > 0) {
71. if (i == this.page) {
72. strHtml += '<span title="Page ' + i + '">[' + i + ']</span>';
73. } else {
74. strHtml += '<span title="Page ' + i + '"><a href="javascript:' + this.name + '.toPage(' + i + ');">[' + i + ']</a></span>';
75. }
76. }
77. }
78. if (nextPage > this.pageCount) {
79. strHtml += '<span title="Next Page">›</span>';
80. strHtml += '<span title="Last Page">»</span>';
81. } else {
82. strHtml += '<span title="Next Page"><a href="javascript:' + this.name + '.toPage(' + nextPage + ');">›</a></span>';
83. strHtml += '<span title="Last Page"><a href="javascript:' + this.name + '.toPage(' + this.pageCount + ');">»</a></span>';
84. }
85. strHtml += '</span><br />';
86. break;
87. case 1 : //模式1 (10页缩略,首页,前页,后页,尾页)
88. strHtml += '<span class="count">Pages: ' + this.page + ' / ' + this.pageCount + '</span>';
89. strHtml += '<span class="number">';
90. if (prevPage < 1) {
91. strHtml += '<span title="First Page">«</span>';
92. strHtml += '<span title="Prev Page">‹</span>';
93. } else {
94. strHtml += '<span title="First Page"><a href="javascript:' + this.name + '.toPage(1);">«</a></span>';
95. strHtml += '<span title="Prev Page"><a href="javascript:' + this.name + '.toPage(' + prevPage + ');">‹</a></span>';
96. }
97. if (this.page % 10 ==0) {
98. var startPage = this.page - 9;
99. } else {
100. var startPage = this.page - this.page % 10 + 1;
101. }
102. if (startPage > 10) strHtml += '<span title="Prev 10 Pages"><a href="javascript:' + this.name + '.toPage(' + (startPage - 1) + ');">...</a></span>';
103. for (var i = startPage; i < startPage + 10; i++) {
104. if (i > this.pageCount) break;
105. if (i == this.page) {
106. strHtml += '<span title="Page ' + i + '">[' + i + ']</span>';
107. } else {
108. strHtml += '<span title="Page ' + i + '"><a href="javascript:' + this.name + '.toPage(' + i + ');">[' + i + ']</a></span>';
109. }
110. }
111. if (this.pageCount >= startPage + 10) strHtml += '<span title="Next 10 Pages"><a href="javascript:' + this.name + '.toPage(' + (startPage + 10) + ');">...</a></span>';
112. if (nextPage > this.pageCount) {
113. strHtml += '<span title="Next Page">›</span>';
114. strHtml += '<span title="Last Page">»</span>';
115. } else {
116. strHtml += '<span title="Next Page"><a href="javascript:' + this.name + '.toPage(' + nextPage + ');">›</a></span>';
117. strHtml += '<span title="Last Page"><a href="javascript:' + this.name + '.toPage(' + this.pageCount + ');">»</a></span>';
118. }
119. strHtml += '</span><br />';
120. break;
121. case 2 : //模式2 (前后缩略,页数,首页,前页,后页,尾页)
122. strHtml += '<span class="count">Pages: ' + this.page + ' / ' + this.pageCount + '</span>';
123. strHtml += '<span class="number">';
124. if (prevPage < 1) {
125. strHtml += '<span title="First Page">«</span>';
126. strHtml += '<span title="Prev Page">‹</span>';
127. } else {
128. strHtml += '<span title="First Page"><a href="javascript:' + this.name + '.toPage(1);">«</a></span>';
129. strHtml += '<span title="Prev Page"><a href="javascript:' + this.name + '.toPage(' + prevPage + ');">‹</a></span>';
130. }
131. if (this.page != 1) strHtml += '<span title="Page 1"><a href="javascript:' + this.name + '.toPage(1);">[1]</a></span>';
132. if (this.page >= 5) strHtml += '<span>...</span>';
133. if (this.pageCount > this.page + 2) {
134. var endPage = this.page + 2;
135. } else {
136. var endPage = this.pageCount;
137. }
138. for (var i = this.page - 2; i <= endPage; i++) {
139. if (i > 0) {
140. if (i == this.page) {
141. strHtml += '<span title="Page ' + i + '">[' + i + ']</span>';
142. } else {
143. if (i != 1 && i != this.pageCount) {
144. strHtml += '<span title="Page ' + i + '"><a href="javascript:' + this.name + '.toPage(' + i + ');">[' + i + ']</a></span>';
145. }
146. }
147. }
148. }
150. if (this.page != this.pageCount) strHtml += '<span title="Page ' + this.pageCount + '"><a href="javascript:' + this.name + '.toPage(' + this.pageCount + ');">[' + this.pageCount + ']</a></span>';
151. if (nextPage > this.pageCount) {
152. strHtml += '<span title="Next Page">›</span>';
153. strHtml += '<span title="Last Page">»</span>';
154. } else {
155. strHtml += '<span title="Next Page"><a href="javascript:' + this.name + '.toPage(' + nextPage + ');">›</a></span>';
156. strHtml += '<span title="Last Page"><a href="javascript:' + this.name + '.toPage(' + this.pageCount + ');">»</a></span>';
157. }
158. strHtml += '</span><br />';
159. break;
160. case 3 : //模式3 (箭头样式,首页,前页,后页,尾页) (only IE)
161. strHtml += '<span class="count">Pages: ' + this.page + ' / ' + this.pageCount + '</span>';
162. strHtml += '<span class="arrow">';
163. if (prevPage < 1) {
164. strHtml += '<span title="First Page">9</span>';
165. strHtml += '<span title="Prev Page">7</span>';
166. } else {
167. strHtml += '<span title="First Page"><a href="javascript:' + this.name + '.toPage(1);">9</a></span>';
168. strHtml += '<span title="Prev Page"><a href="javascript:' + this.name + '.toPage(' + prevPage + ');">7</a></span>';
169. }
170. if (nextPage > this.pageCount) {
171. strHtml += '<span title="Next Page">8</span>';
172. strHtml += '<span title="Last Page">:</span>';
173. } else {
174. strHtml += '<span title="Next Page"><a href="javascript:' + this.name + '.toPage(' + nextPage + ');">8</a></span>';
175. strHtml += '<span title="Last Page"><a href="javascript:' + this.name + '.toPage(' + this.pageCount + ');">:</a></span>';
176. }
177. strHtml += '</span><br />';
178. break;
179. case 4 : //模式4 (下拉框)
180. if (this.pageCount < 1) {
181. strHtml += '<select name="toPage" disabled>';
182. strHtml += '<option value="0">No Pages</option>';
183. } else {
184. var chkSelect;
185. strHtml += '<select name="toPage" onchange="' + this.name + '.toPage(this);">';
186. for (var i = 1; i <= this.pageCount; i++) {
187. if (this.page == i) chkSelect=' selected="selected"';
188. else chkSelect='';
189. strHtml += '<option value="' + i + '"' + chkSelect + '>Pages: ' + i + ' / ' + this.pageCount + '</option>';
190. }
191. }
192. strHtml += '</select>';
193. break;
194. case 5 : //模式5 (输入框)
195. strHtml += '<span class="input">';
196. if (this.pageCount < 1) {
197. strHtml += '<input type="text" name="toPage" value="No Pages" class="itext" disabled="disabled">';
198. strHtml += '<input type="button" name="go" value="GO" class="ibutton" disabled="disabled"></option>';
199. } else {
200. strHtml += '<input type="text" value="Input Page:" class="ititle" readonly="readonly">';
201. strHtml += '<input type="text" id="pageInput' + this.showTimes + '" value="' + this.page + '" class="itext" title="Input page" onkeypress="return ' + this.name + '.formatInputPage(event);" onfocus="this.select()">';
202. strHtml += '<input type="text" value=" / ' + this.pageCount + '" class="icount" readonly="readonly">';
203. strHtml += '<input type="button" name="go" value="GO" class="ibutton" onclick="' + this.name + '.toPage(document.getElementById(\'pageInput' + this.showTimes + '\').value);"></option>';
204. }
205. strHtml += '</span>';
206. break;
207. default :
208. strHtml = 'Javascript showPage Error: not find mode ' + mode;
209. break;
210. }
211. return strHtml;
212. }
213. showPages.prototype.createUrl = function (page) { //生成页面跳转url
214. if (isNaN(parseInt(page))) page = 1;
215. if (page < 1) page = 1;
216. if (page > this.pageCount) page = this.pageCount;
217. var url = location.protocol + '//' + location.host + location.pathname;
218. var args = location.search;
219. var reg = new RegExp('([\?&]?)' + this.argName + '=[^&]*[&$]?', 'gi');
220. args = args.replace(reg,'$1');
221. if (args == '' || args == null) {
222. args += '?' + this.argName + '=' + page;
223. } else if (args.substr(args.length - 1,1) == '?' || args.substr(args.length - 1,1) == '&') {
224. args += this.argName + '=' + page;
225. } else {
226. args += '&' + this.argName + '=' + page;
227. }
228. return url + args;
229. }
230. showPages.prototype.toPage = function(page){ //页面跳转
231. var turnTo = 1;
232. if (typeof(page) == 'object') {
233. turnTo = page.options[page.selectedIndex].value;
234. } else {
235. turnTo = page;
236. }
237. self.location.href = this.createUrl(turnTo);
238. }
239. showPages.prototype.printHtml = function(mode){ //显示html代码
240. this.getPage();
241. this.checkPages();
242. this.showTimes += 1;
243. document.write('<div id="pages_' + this.name + '_' + this.showTimes + '" class="pages"></div>');
244. document.getElementById('pages_' + this.name + '_' + this.showTimes).innerHTML = this.createHtml(mode);
245.
246. }
247. showPages.prototype.formatInputPage = function(e){ //限定输入页数格式
248. var ie = navigator.appName=="Microsoft Internet Explorer"?true:false;
249. if(!ie) var key = e.which;
250. else var key = event.keyCode;
251. if (key == 8 || key == 46 || (key >= 48 && key <= 57)) return true;
252. return false;
253. }
254. //-->
256. <style>
257. /* Pages Main Tyle */
258. .pages {
259. color: #000000;
260. cursor: default;
261. font-size: 10px;
262. font-family: Tahoma, Verdana;
263. padding: 3px 0px 3px 0px;
264. }
265. .pages .count, .pages .number, .pages .arrow {
266. color: #000000;
267. font-size: 10px;
268. background-color: #F7F7F7;
269. border: 1px solid #CCCCCC;
270. }
271. /* Page and PageCount Style */
272. .pages .count {
273. font-weight: bold;
274. border-right: none;
275. padding: 2px 10px 1px 10px;
276. }
277. /* Mode 0,1,2 Style (Number) */
278. .pages .number {
279. font-weight: normal;
280. padding: 2px 10px 1px 10px;
281. }
282. .pages .number a, .pages .number span {
283. font-size: 10px;
284. }
285. .pages .number span {
286. color: #999999;
287. margin: 0px 3px 0px 3px;
288. }
289. .pages .number a {
290. color: #000000;
291. text-decoration: none;
292. }
293. .pages .number a:hover {
294. color: #0000ff;
295. }
296. /* Mode 3 Style (Arrow) */
297. .pages .arrow {
298. font-weight: normal;
299. padding: 0px 5px 0px 5px;
300. }
301. .pages .arrow a, .pages .arrow span {
302. font-size: 10px;
303. font-family: Webdings;
304. }
305. .pages .arrow span {
306. color: #999999;
307. margin: 0px 5px 0px 5px;
308. }
309. .pages .arrow a {
310. color: #000000;
311. text-decoration: none;
312. }
313. .pages .arrow a:hover {
314. color: #0000ff;
315. }
316. /* Mode 4 Style (Select) */
317. .pages select, .pages input {
318. color: #000000;
319. font-size: 10px;
320. font-family: Tahoma, Verdana;
321. }
322. /* Mode 5 Style (Input) */
323. .pages .input input.ititle, .pages .input input.itext, .pages .input input.icount {
324. color: #666666;
325. font-weight: bold;
326. background-color: #F7F7F7;
327. border: 1px solid #CCCCCC;
328. }
329. .pages .input input.ititle {
330. width: 70px;
331. text-align: right;
332. border-right: none;
333. }
334. .pages .input input.itext {
335. width: 25px;
336. color: #000000;
337. text-align: right;
338. border-left: none;
339. border-right: none;
340. }
341. .pages .input input.icount {
342. width: 35px;
343. text-align: left;
344. border-left: none;
345. }
346. .pages .input input.ibutton {
347. height: 17px;
348. color: #FFFFFF;
349. font-weight: bold;
350. font-family: Verdana;
351. background-color: #999999;
352. border: 1px solid #666666;
353. padding: 0px 0px 2px 1px;
354. margin-left: 2px;
355. cursor: hand;
356. }
357.
358. /* body */
359. body {
360. font-size: 12px;
361. }
362. </style>
363. </head>
364.
365. <body>
366. <script language="JavaScript">
367. <!--
368. var pg = new showPages('pg');
369. pg.pageCount =12; // 定义总页数(必要)
370. //pg.argName = 'p'; // 定义参数名(可选,默认为page)
371.
372. document.write('<br>Show Times: ' + pg.showTimes + ', Mood Default');
373. pg.printHtml();
374. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 0');
375. pg.printHtml(0);
376. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 1');
377. pg.printHtml(1);
378. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 2');
379. pg.printHtml(2);
380. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 3 (only IE)');
381. pg.printHtml(3);
382. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 4');
383. pg.printHtml(4);
384. document.write('<br>Show Times: ' + pg.showTimes + ', Mood 5');
385. pg.printHtml(5);
386. //-->
387. </script>
388. </body>
389. </html>
alter procedure fengye
@pagesize int, @pageindex int --pagesize 是每页的条数记录 pageindex是当前页数--
as
declare @jilu int --当前页数的条数记录--
else ----
begin
set @jilu=@pageindex*@pagesize -- 不在前多少条记录 --
exec(' select top '+@pagesize+' * from SchoolmateInfo where id not in (select top
'+@jilu+' id from SchoolmateInfo order by id asc) order by id asc ')
end