SQL学习-随机数与分页

print '================================================================================
初始化数据库：
================================================================================'

USE master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='aDB')
DROP DATABASE aDB
GO
EXEC XP_cmdshell 'mkdir D:\project',no_output
CREATE DATABASE aDB
ON
(
  NAME='aDB_data',
  FILENAME='D:\project\aDB_data.mdf',
  SIZE =10 MB,
  FILEGROWTH=20%

)
LOG ON
(
  NAME='aDB_log',
  FILENAME='d:\project\aDB_log.ldf',
  SIZE =20 MB,
  FILEGROWTH=10%
)
GO

 

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,           --最多显示页数，默认5页
@rowsNum int=10,         --每页行数，默认10行
@rowsSum int=50         --总行数，默认为50行，随机生成
as
if (not @rowsNum between 1 and @rowsSum) or (@maxPage<= 0 ) or(@rowsSum<= 0 )
begin
raiserror ('错误！请正确指定各项参数！',16,1)
return
end  
IF EXISTS(SELECT * FROM sysobjects WHERE name='A')
DROP table A
create table A
(
ID int identity(1,1) not null,
Name varchar(10) not null                    
)
declare @n int
set @n=1
while (@n<=@rowsSum)
begin
insert into A values(substring(replace(newid(),'-',''),1,5))
set @n=@n+1
end

declare @pages int,@pageNo int,@lastRows int              --定义变量总页数，页码，最后一页行数
--select @rowsSum=count(*) from A
select @pages=ceiling(@rowsSum*1.0/@rowsNum)
select @lastRows=@rowsSum%@rowsNum
--select pages=30%4
         

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)
 if (@lastRows=0)
  begin
   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
 else
        begin  
            while (@pageNo<=@pages)
   begin
   print '-------------------第'+convert(varchar(10),@pageNo)+'页------------------'
    if(@pageNo<>@pages)
    begin
    select top (@rowsNum) * from A
    where (ID not in (select top (@rowsNum*(@pages-@pageNo-1)+@lastRows) 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
    end
                else
                select top (@lastRows) * from A
                where (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
                order by ID
   set @pageNo=@pageNo+1
   end
        end
end
GO

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