好朋友今天写的[随机数与分页]

print ‘============================================================================================

自己摸索的分页方法

============================================================================================’

USE aDB
GO
SET NOCOUNT ON

IF EXISTS(SELECT * FROM sysobjects WHERE name='A')
DROP table A
GO
create table A
(
ID int identity(1,1) not null,
LastUpdateDate datetime not null                    
)
declare @n int
set @n=1
while (@n<=50)
begin
insert into A values(dateadd(dd,datediff(dd,'2000-01-01','2009-08-3')*RAND(),'2000-01-01'))
set @n=@n+1
end
print '================================================================================================='
GO

declare @id int,@date datetime
select TOP 1 @id=ID,@date=LastUpdateDate from A order by LastUpdateDate desc
print '最近的交易号：'+convert(varchar(5),@id)+'    日期为'+convert(varchar(10),@date,111)
GO

print '================================================================================================='
print '取出表A中第31—40记录：'
print '================================================================================================='
select top 10 * from (select top 40 * from A order by ID ) as a order by ID desc
print '================================================================================================='
print '表A的数据如下：'
print '================================================================================================='
select * from A

 

print '================================================================================================='
print '下面是分页的实现：'
print '================================================================================================='

USE aDB
GO
SET NOCOUNT ON

if exists (select * from sysobjects where name = 'proc_page')
drop procedure proc_page
GO
create procedure proc_page
@maxPage int=5,           --最多显示页数
@rowsNum int=10         --每页行数，默认10行
as
declare @pages int,@pageNo int,@rowsSum int                --总页数，页码，总行数
select @rowsSum=count(*) from A
select @pages=ceiling(@rowsSum*1.0/@rowsNum)
if (not @rowsNum between 0 and @rowsSum)
begin
raiserror ('错误！请正确指定每页行数！',16,1)
return
end            

if (@maxPage<@pages)
begin
select @rowsSum=@rowsNum*@maxPage
set @pageNo = 1
print '分页显示:一共'+convert(varchar(10),@rowsSum)+'行，每页行数'+convert(varchar(10),@rowsNum)+'，总页数'+convert(varchar(10),@maxPage)
 while (@pageNo<=@maxPage)
 begin
 print '-------------------第'+convert(varchar(10),@pageNo)+'页------------------'
    if exists (select * from sysobjects where name='newA')
    drop table newA
  
 select top (@rowsNum) a.* from  (select top (@rowsSum) * from A order by ID) a
 where (ID not in (select top (@rowsNum*(@maxPage-@pageNo)) a.ID from a order by a.ID desc))
 and
 (ID not in (select top (@rowsNum*(@pageNo-1)) a.ID from a order by a.ID))
 order by a.ID

 set @pageNo=@pageNo+1
 end
end

else
begin

set @pageNo = 1
print '分页显示:一共'+convert(varchar(10),@rowsSum)+'行，每页行数'+convert(varchar(10),@rowsNum)+'，总页数'+convert(varchar(10),@pages)
 while (@pageNo<=@pages)
 begin
 print '-------------------第'+convert(varchar(10),@pageNo)+'页------------------'

 select top (@rowsNum) * from A
 where (ID not in (select top (@rowsNum*(@pages-@pageNo)) ID from A order by ID desc))
 and
 (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
 order by ID
 set @pageNo=@pageNo+1
 end
end
GO

EXEC proc_page
EXEC proc_page 3
EXEC proc_page 3,5
EXEC proc_page @rowsNum=3
EXEC proc_page 10,4
EXEC proc_page 50
EXEC proc_page @rowsNum=100    --产生错误

 

 原文地址：http://blog.sina.com.cn/s/blog_615b2d3f0100eoze.html