sqlserver 也可以基于伪列分页！

时间：2011-05-25 来源：darjuan

与oracle一样 sqlserver 2005后出现借助函数ROW_NUMBER()伪列，可以简单的通过子查询实现数据分页

Orcale 分页可以看一下这一篇简单搞一下 Oracle 存储过程动态SQL之获取查询分页！

先简单试一下

 SELECT * FROM 
 (
   SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test 
 )   as temp where Row between  1 and 10

看来还行......

继续改写一下：

declare 
@v_index int,
@v_size int
begin
 set @v_index = 1; 
 set @v_size = 10;
 SELECT * FROM 
 (
        SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from test 
 ) 
 as temp where Row 
            between (@v_index-1)*@v_size+1 　　　　　　　 and (@v_index-1)*@v_size+@v_size
end;

用with 语句再进行改写一下，模仿别人写成存储过程

create procedure proc_get_test_list 
(
    @p_index int,   
    @p_size     int
 ) 
as
begin

 with temptb as
 (
   select row_number() over (order by id desc) as  row, * from test 
 )

 select * from temptb where row between 

(@p_index-1)*@p_size+1 and (@p_index-1)*@p_size+@p_size

end