1.有標(biāo)識列時的方案:
--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 頁大小*(頁數(shù)-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 頁大小*(頁數(shù)-1) id
???????????????? FROM 表
???????????????? ORDER BY id) AS T))
ORDER BY ID
--3.比較當(dāng)前頁主鍵大小
使用:不能指定分頁.只適用於 首頁,上一頁,下一頁,末頁.
首頁:
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.無標(biāo)識列時的方案,同時也適用於有標(biāo)識列時的情況.
--1.利用SQL的游標(biāo)存儲過程分頁
create procedure SqlPager
@sqlstr nvarchar(4000), --查詢字符串
@currentpage int, --第N頁
@pagesize int --每頁行數(shù)
as
set nocount on
declare @P1 int, --P1是游標(biāo)的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數(shù)--,@rowcount as 總行數(shù),@currentpage as 當(dāng)前頁
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

sqlserver中分頁方法集錦