SQL分页方法

表中主键必须为标识列，[ID] int IDENTITY (1,1)
也可以使用联合主键 id+id2+id3+……

 

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

 

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用

 

----------------------------------------------------------------------------------

 

1、“俄罗斯存储过程”的改良版
CREATE procedure pagination1
(@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>dataadd(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 TABEL #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表的关键字

使用了not in 而not in 是无法使用索引的，效率上还是差了一点。

 

 

3. max的方法：
SELECT TOP 页大小 *
FROM TABLE 1
WHERE id>
(SELECT MAX(id) FROM
(SELECT TOP((页码-1)*页大小)id FROM TABLE 1 ORDER BY id) as T)

 

4. 定位法

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 ORDERBY newsID DESC
SET ROWCOUNT 0

 

单字段排序，排序字段的值不能重复

 

5. 颠颠倒倒法
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是排序字段
必须有主键

 

 

----------------------------------------------------------------------------------

 

SELECT top 页大小*
FROM TABLE 1
WHERE id >
(SELECT MAX(id) FROM
(SELCT TOP((页码-1)*页大小)id FROM TABLE 1 ORDER BY id)AS T)
ORDER BY id

--获取指定页的数据
CREATE PROCEDURE pagination3
@tblName varchar(255), --表名
@strGetFields varchar(1000)='*', --需要返回的列
@fldName varchar(255)='', --排序的字段名
@PageSize int =10, --页尺寸
@PageIndex int =1, --页码，
@doCount bit =0, --返回记录总数，非0值则返回
@OrderType bit =0, --设置排序类型，非0值则降序
@strWhere varchare(1500)='' --查询条件（注意：不要加where）
AS
DECLARE @strSQL varchar(5000) --主语句
DECLARE @strTemp varchar(110) --临时变量
DECLARE @strOrder varchar(400) --排序类型

IF @doCount!=0
BEGIN
 IF @strWhere!=''
 SET @strSQL='SELECT COUNT(*) AS Total FROM ["+@tblname+"]WHERE "+@strWhere+"
 ELSE
 SET @strSQL ="SELECT COUNT(*) AS Total FROM ["+@tblName+"]"
END
--如果@doCount传回来的不是0，就执行总数统计。以下所有代码 都是@doCount为0的情况
ELSE
BEGIN

IF @OrderType !=0
BEGIN
  SET @strTemp ="<(SELECT MIN"
  SET @strOrder ="ORDER BY["+@fldname+"]DESC"
--如果@OrderType不是0，就执行降序，这句很重要！
END
ELSE
BEGIN
  SET @strTemp =">(SELECT MAX"
  SET @strOrder ="ORDER BY ["+@fldName+"]ASC"
END

IF @PageIndex =1
BEGIN
  IF @strWhere !=''
  SET @strSQL = "SELECT TOP"+str(@PageSize)+""+@strGetFields+"from["+@tblname+"]
WHERE "+@strWhere+"+""+@strOrder
  ELSE
  SET @strSQL = "SELECT TOP"+STR(@PageSize)+""+@strGetFields+FROM["+@tblname+"]"+@strOrder
--如果第一页就执行以上代码，这样会加快执行速度
END
ELSE
BEGIN
--以下赋予了@strSQL以真正执行的SQL代码
SET @strSQL ="SELECT TOP"+str(@PageSize)+""+@strGetFields+"FROM["
+@tblName+"]WHERE["+@fldName+"]"+@strTemp+"(["+@fldname+"])FROM
(SELECT TOP "+str((@PageIndex-1)*@PageSize)+"["+@fldName+"]FROM["+@tblNmae+"]"+@strOrder+")AS tblTemp"+@strOrder

IF @strWhere !=''
  SET @strSQL = "SELECT TOP" +STR(@PageSize)+""+@strGetFields+"FROM["
  +@tblname+"]WHERE["+@fldName+"]"+@strTemp+"(["
  +@fldName+"])FROM(SELECT TOP "+str((@PageIndex-1)*@PageSize)+"[
  +@fldName+"]FROM["+@tblname+"[WHERE"+@strWhere+""
  +@strOrder +")AS tblTemp)AND "+ @strWhere+""+@strOrder
END
END
EXEC(@strSQL)
GO