1.有標(biāo)識(shí)列時(shí)的方案:
--1.利用Not In和SELECT TOP分頁(yè)
語(yǔ)句形式：??
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
????????? (SELECT TOP 20 id
???????? FROM TestTable
???????? ORDER BY id))
ORDER BY ID

SELECT TOP 頁(yè)大小 *
FROM TestTable
WHERE (ID NOT IN
????????? (SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1) id
???????? FROM 表
???????? ORDER BY id))
ORDER BY ID
--2.利用ID大于多少和SELECT TOP分頁(yè)
語(yǔ)句形式：
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 頁(yè)大小 *
FROM TestTable
WHERE (ID >
????????? (SELECT MAX(id)
???????? FROM (SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1) id
???????????????? FROM 表
???????????????? ORDER BY id) AS T))
ORDER BY ID
--3.比較當(dāng)前頁(yè)主鍵大小
使用:不能指定分頁(yè).只適用於 首頁(yè),上一頁(yè),下一頁(yè),末頁(yè).
首頁(yè):
select top 頁(yè)大小 * from 表名 order by 主鍵
下一頁(yè):
select top 頁(yè)大小 * from 表名 where 主鍵 > 上一頁(yè)末記錄的主鍵 order by 主鍵
上一頁(yè):
select top 頁(yè)大小 * from 表名 where 主鍵 > 上一頁(yè)首記錄的主鍵 order by 主鍵 desc
末頁(yè):
select top 頁(yè)大小 * from 表名 order by 主鍵 desc

2.無(wú)標(biāo)識(shí)列時(shí)的方案,同時(shí)也適用於有標(biāo)識(shí)列時(shí)的情況.
--1.利用SQL的游標(biāo)存儲(chǔ)過(guò)程分頁(yè)
create procedure SqlPager
@sqlstr nvarchar(4000), --查詢(xún)字符串
@currentpage int, --第N頁(yè)
@pagesize int --每頁(yè)行數(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 總頁(yè)數(shù)--,@rowcount as 總行數(shù),@currentpage as 當(dāng)前頁(yè)
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

--2.用臨時(shí)表.
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中分頁(yè)方法集錦