SQL SERVER 2008 中分页方法集锦

hwl198212

1.有标识列時的方案:
--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 页大小*(页数-1) 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 页大小*(页数-1) id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID
--3.比較當前頁主鍵大小
使用:不能指定分頁.只適用於 首頁,上一頁,下一頁,末頁.
首页:
select top 页大小 * from 表名 order by 主键
下一頁:
select top 页大小 * from 表名 where 主键 > 上一页末记录的主键 order by 主键
上一頁:
select top 页大小 * from 表名 where 主键 > 上一页首记录的主键 order by 主键 desc
末頁:
select top 页大小 * from 表名 order by 主键 desc

2.無標識列時的方案,同時也適用於有標識列時的情況.
--1.利用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

--2.用临时表.
select top 10 * into #tmp from categories
select * from #tmp

3.SQL2005中的新的排序方法
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY ItemID) AS RowNo
FROM Portal_Discussion
) AS A
WHERE RowNo>=1 and RowNo<=10