您的位置:首页 > 数据库 > MSSQL

sql server 存储过程的分页

日期:2006-11-16 14:27:04 点击: 来自: 作者:

建立表: 

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 

More..素材图片 Picture Navigation
MSSQL热门 Class Hot
MSSQL推荐 Class Commend
版权所有:中国网站资源 2005- 未经授权禁止复制或建立镜像 This Site Tech:XHTML+DIV+CSS+Javascript
CopyRight ® 2005- www.chinaddv.com online services. all rights reserved. ICP06016627
Optimized to 1024x768 to Firefox,Netscape,Opera,MS-IE6+.