高效分页存储过程 花一段时间写了个分页存储过程
此存储过程可产生3形式中SQL语句形式一(主要实现对无主键表的分页,可以任意字段排序,但是效率很低)exec 'articles',@Orderfld='articleid',@PageIndex=2
SELECT * FROM (SELECT TOP 10 * FROM
(SELECT TOP 20* FROM articles ORDER BY articleid ASC ) TB2
Order by articleid desc ) TB3
ORDER BY articleid ASC
形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)exec 'articles',@Orderfld='Title',@PageIndex=2,@FKColumn='articleid'
SELECT TOP 10*FROM articles WHERE articleid NOT IN
(SELECT TOP 10 articleid FROM articlesORDER BY articleid ASC )
ORDER BY articleid ASC
形式三(适合以唯一主键排序且数据量很大时)exec 'articles',@Orderfld='articleid',@PageIndex=2,@FKColumn='articleid'
SELECT TOP 10*FROM articles WHERE(articleid>(SELECT MAX(articleid)
FROM (SELECT TOP 10 articleid FROM articlesORDER BY articleid ASC ) AS T))
ORDER BY articleid ASC
页:
[1]