高效分页存储过程 花一段时间写了个分页存储过程

阿杰

此存储过程可产生3形式中SQL语句

形式一(主要实现对无主键表的分页,可以任意字段排序,但是效率很低)

1. exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2
   
2. 
   
3. SELECT * FROM (SELECT TOP 10 * FROM
   
4. (SELECT TOP 20  * FROM articles ORDER BY articleid ASC ) TB2
   
5. Order by articleid desc ) TB3
   
6. ORDER BY articleid ASC
   

复制代码

形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)

1. exec [PROC_Pagination] 'articles',@Orderfld='Title',@PageIndex=2,@FKColumn='articleid'
   
2. 
   
3. SELECT TOP 10  *  FROM articles WHERE articleid NOT IN
   
4. (SELECT TOP 10 articleid FROM articles  ORDER BY articleid ASC )  
   
5. ORDER BY articleid ASC
   

复制代码

形式三(适合以唯一主键排序且数据量很大时)

1. exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2,@FKColumn='articleid'
   
2. 
   
3. SELECT TOP 10  *  FROM articles WHERE(articleid>(SELECT MAX(articleid)
   
4. FROM (SELECT TOP 10 articleid FROM articles  ORDER BY articleid ASC ) AS T))  
   
5. ORDER BY articleid ASC

复制代码