SQL SERVER 2008 中分页方法集锦

<span style="font-size: small">1.有标识列時的方案:
<br />--1.利用Not In和SELECT TOP分页
<br />语句形式：  
<br />SELECT TOP 10 *
<br />FROM TestTable
<br />WHERE (ID NOT IN
<br />          (SELECT TOP 20 id
<br />         FROM TestTable
<br />         ORDER BY id))
<br />ORDER BY ID
<br />
<br />
<br />SELECT TOP 页大小 *
<br />FROM TestTable
<br />WHERE (ID NOT IN
<br />          (SELECT TOP 页大小*(页数-1) id
<br />         FROM 表
<br />         ORDER BY id))
<br />ORDER BY ID
<br />--2.利用ID大于多少和SELECT TOP分页
<br />语句形式：
<br />SELECT TOP 10 *
<br />FROM TestTable
<br />WHERE (ID >
<br />          (SELECT MAX(id)
<br />         FROM (SELECT TOP 20 id
<br />                 FROM TestTable
<br />                 ORDER BY id) AS T))
<br />ORDER BY ID
<br />
<br />
<br />SELECT TOP 页大小 *
<br />FROM TestTable
<br />WHERE (ID >
<br />          (SELECT MAX(id)
<br />         FROM (SELECT TOP 页大小*(页数-1) id
<br />                 FROM 表
<br />                 ORDER BY id) AS T))
<br />ORDER BY ID
<br />--3.比較當前頁主鍵大小
<br />使用:不能指定分頁.只適用於 首頁,上一頁,下一頁,末頁.
<br />首页:
<br />select top 页大小 * from 表名 order by 主键
<br />下一頁:
<br />select top 页大小 * from 表名 where 主键 > 上一页末记录的主键 order by 主键
<br />上一頁:
<br />select top 页大小 * from 表名 where 主键 > 上一页首记录的主键 order by 主键 desc
<br />末頁:
<br />select top 页大小 * from 表名 order by 主键 desc
<br />
<br />2.無標識列時的方案,同時也適用於有標識列時的情況.
<br />--1.利用SQL的游标存储过程分页
<br />create procedure SqlPager
<br />@sqlstr nvarchar(4000), --查询字符串
<br />@currentpage int, --第N页
<br />@pagesize int --每页行数
<br />as
<br />set nocount on
<br />declare @P1 int, --P1是游标的id
<br />@rowcount int
<br />exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
<br />select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
<br />set @currentpage=(@currentpage-1)*@pagesize+1
<br />exec sp_cursorfetch @P1,16,@currentpage,@pagesize
<br />exec sp_cursorclose @P1
<br />set nocount off
<br />
<br />--2.用临时表.
<br />select top 10 * into #tmp from categories
<br />select * from #tmp
<br />
<br />3.SQL2005中的新的排序方法
<br />SELECT *
<br />FROM
<br />(
<br />SELECT *,ROW_NUMBER() OVER (ORDER BY ItemID) AS RowNo
<br />FROM Portal_Discussion
<br />) AS A
<br />WHERE RowNo>=1 and RowNo<=10</span>