.net 面试题之 Sql 分页 存储过程

--三种分页方式 据说 第二种最快 其次 第三种 最后是第一种

 

 

第一种方法 
 1 --1. select top *  not  in 
 2 
 3 alter proc feiye_proc
 4 (
 5 @pageindex int ,
 6 @pagesize int
 7 )
 8 as
 9 begin
10 declare @strSql  varchar(200)
11 set @strsql  = 'select top '+cast(@pagesize as varchar(10))+' ProductsName,Price,Weight ,Color  from dbo.t_Products where  ProductsID not in(select top '+cast((@pageindex-1) * @pagesize as varchar(10))+' ProductsID from dbo.t_Products order by ProductsID asc) order by ProductsID asc'
12 print @strsql 
13 end 
14 feiye_proc 2,2 
15 select top 2 ProductsName,Price,Weight ,Color  from dbo.t_Products where  ProductsID not in(select top 2 ProductsID from dbo.t_Products order by ProductsID asc) order by ProductsID asc

 

第二种方法 id > max(id) 
--2.   id> max (id)
create proc fenye2_proc
(
@pageindex int,
@pagesize int 
)
as
begin
declare @strSql varchar(200)
set @strSql='select top '+cast(@pagesize as varchar(10))+' * from dbo.t_Products where ProductsID>(select max(ProductsID) from (select top '+cast((@pageindex-1)*@pagesize as varchar(10))+'ProductsID  from t_Products ) as b)'
print @strsql 
end 
exec fenye2_proc 2,2

 

 

 

第三种方法 Row_number() 函数 
--3. row_number()
create procedure fenye3_proc
(
@pagesize int,
@pageindex int 
)
as
begin 
declare @strSql varchar(200)  --定义执行的sql  

set @strSql='select * from (select row_number() over(order by ProductsID asc) rid,* from dbo.t_Products) as b 
where b.rid >'+ cast((@pageindex-1)*@pagesize as varchar(10))+' and  b.rid<='+ cast(@pageindex*@pagesize as varchar(10))
print @strSql
end

 

 

 

 

 

 

posted @ 2013-03-21 16:55  hzy168  阅读(282)  评论(0)  编辑  收藏  举报