用oracle中的Row_Number實現分頁

標籤：

1：首先是

    select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1

   產生帶序號的集合

2：再查詢該集合的 第 1  到第 5條資料

    select * from 
    (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp
    where rowNumber between 1 and 5

完整的Sql語句

declare @pagesize int; declare @pageindex int; set @pagesize = 3

set @pageindex = 1; --第一頁

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 2; --第二頁

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 3; --第三頁

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 4;--第四頁

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

 

下面我們來寫個預存程序分頁

------------------------------

 

Alter Procedure PagePager   

@TableName varchar(80),   

@File varchar(1000),---   

@Where varchar(500),---帶and串連   

@OrderFile varchar(100), -- 排序欄位   

@OrderType varchar(10),--asc:順序，desc:倒序   

@PageSize varchar(10), --   

@PageIndex varchar(10) --  

as      

if(ISNULL(@OrderFile, ‘‘) = ‘‘)   

begin     

set @OrderFile = ‘ID‘;   

end   

if(ISNULL(@OrderType,‘‘) = ‘‘)   

begin     

set @OrderType = ‘asc‘   

end   

if(ISNULL(@File,‘‘) = ‘‘)   

begin     

set @File = ‘*‘   

end      

declare @select varchar(8000)   

set @select = ‘    select ‘ + @File + ‘ from     (       select *,ROW_NUMBER() over(order by ‘ + @OrderFile + ‘  ‘+ @OrderType + ‘) as ‘‘rowNumber‘‘    from ‘ + @TableName + ‘    where 1=1 ‘ + @Where + ‘     )  temp where  rowNumber between (((‘ + @PageIndex + ‘ - 1) * ‘ + @PageSize + ‘)+1) and (‘ + @PageIndex + ‘*‘+ @PageSize+‘)‘   

exec(@select)

用oracle中的Row_Number實現分頁