1 通过select top进行分页查询 /*查询原理:需要查询第N页时,首先取到前N页的所有数据,然后排除前N-1页的数据,就是第N页的数据*/
create PROCEDURE GetDataWithPage
(
@pageIndex int = 1, -- 页码
@pageSize int = 20, -- 页尺寸
)
as
begin
if @pageIndex < 1
begin
Set @pageIndex=1
end
--如果是第一页时要执行的语句
if @PageIndex = 1
begin
select top ((@PageIndex)*@PageSize)
field1,field2,--查询字段
fieldOrderby --排序字段,按什么字段分页的字段,建议使用主键,或者唯一键
from tableName --查询表名称
where id>100 --查询条件
order by fieldOrderby --排序字段,按什么字段分页的字段,
select count(*) as Total from tableName where id>100--返回总记录数
end
else
begin
select top ((@PageIndex)*@PageSize) --取出前PageIndex页的数据
field1,field2,--查询字段
fieldOrderby --排序字段,按什么字段分页的字段,建议使用主键,或者唯一键
into #tempTable --插入临时表
from tableName --查询表名称
where id>100 --查询条件
order by fieldOrderby --排序字段,按什么字段分页的字段,
-----取出前pageIndex页数据插入临时表
------排除前pageIndex-1页的数据,取出第pageIndex的数据
select top (@PageIndex) --
field1,field2,--排序字段
fieldOrderby --
from #tempTable --从临时表中取数据
where fieldOrderby --
not in (select top ((@PageIndex-1)*@PageSize) fieldOrderby from #tempTable)
---- 从临时表取出 pageIndex的数据
----可以根据升序或者降序把not in 改为 <min(fieldOrderby)或者>max(fieldOrderby)
select count(*) as Total from tableName where id>100--返回总记录数
end
end
2通过系统存储过程进行分页查询
[sql] view plaincopy
/*
一共返回三个表第一个表为空 查询字符串的条件中有 like ,in 可能出现问题,具体出现原因不明,第二个表包含总页数,总行数,
当前页第三个表包含查询记录
*/
create procedure [dbo].[GetOnePageData]
@sqlstr nvarchar(4000), --查询字符串 ,就是一般的查询语句,不需要top关键字
@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
--定义与游标和游标选项相关联的 SQL 语句,然后填充游标。
--选择总页数,总行数,当前页
select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage
set @currentpage=(@currentpage-1)*@pagesize+1
--查询记录
exec sp_cursorfetch @P1,16,@currentpage,@pagesize --从游标中提取一行或一个行块。
--返回值
exec sp_cursorclose @P1--关闭并释放游标
set nocount off
go
3 通过新函数ROW_NUMBER()进行分页查询
[sql] view plaincopy
/*
适用于sql2005,据调查此方法限制最少,性能最佳
返回两个表,第一个表包含总行数,第二个表是查询到的记录
--分页查询的原理:
--1.先将预分页内容按照排序条件加上自增列导入到临时表中(或表变量)
--2.针对临时表操作,找到相应的N页对应的自增列编码范围
--3.根据第N页对应的自增列编码范围,查找第N页内容
--需要注意的是:
--第一是添加自增列,确定行号
--第二缩减中间处理过程的操作数据量
*/
create proc [dbo].[GetOnePageData]--//
(
@page int,
@pagesize int
)
as
begin
select top (@pagesize*@page) ROW_NUMBER() --可以减少处理的数据,加快速度
OVER(ORDER BY NodeID) as rowNum,--按什么字段分页的字段,不需要唯一性
NodeID,NodeName,ControlIP --查询字段
into #temp --插入临时表
from TableName -- 查询表名称
where NodeID>100 --查询条件
select count(*) from #temp--总记录条数
select * from #temp where rowNum>@pagesize*(@page-1) and rowNum<=@pagesize*@page--
end
综述,如果需要通用的存储过程,可以把第一或者第三种分页方法进行改写,在数据库拼字符串,但性能会差好多。这或许就是通用和性能之间的矛盾吧。