建立表：
CREATE TABLE [testtable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[firstname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[lastname] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
插入數據：(2萬條，用更多的數據測試會明顯一些)
set identity_insert testtable on
declare @i int
set @i=1
while @i<=50000
begin
insert into testtable([id], firstname, lastname, country,note) values(@i, 'firstname_xxx_'+convert(varchar, @i),'astname_xxx_'+convert(varchar, @i),'country_xxx'+convert(varchar, @i),'note_xxx'+convert(varchar, @i))
set @i=@i+1
print @i
end
set identity_insert testtable off
-------------------------------------
分頁方案一：(利用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
-------------------------------------
分頁方案二：(利用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
-------------------------------------
分頁方案三：(利用sql的游標存儲過程分頁)
create procedure xiaozhengge
@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