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

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

--1. select top *  not  in 

alter proc feiye_proc
(
@pageindex int ,
@pagesize int
)
as
begin
declare @strSql  varchar(200)
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'
print @strsql 
end 
feiye_proc 2,2 
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

--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

--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