`

SQLServer的三种分页方案

    博客分类:
  • SQL
阅读更多


第一种方案:
两次top分页,原型如下:  

SELECT   *   FROM   (
SELECT   TOP   页面容量   *   FROM   (

SELECT   TOP   页面容量*当前页码   *   FROM  

表   WHERE   条件   ORDER   BY   字段A   ASC

)   AS   TEMPTABLE1   ORDER   BY   字段A   DESC

)   AS   TEMPTABLE2   ORDER   BY   字段A   ASC

弊病:1   强制排序   否则不能分页,虽然目前基本上查询表都要排序

2.   排序字段不能有空值即null,否则分页结果不符实际情况

3.   多次order   by   速度会快吗,有待我进一步大数据量测试

基于上面的分页原理,我写了一个存储过程,有兴趣的看看,如下;
-----------------------------------------------------------------------------------------------------------
alter   proc   sp_pagination
@tbName   varchar(200),   --表名,可多表,逗号分隔
@tbFields   varchar(500)= '* ',--字段名,如果多表请带前缀
@whereStr   varchar(300)= ' ',   --where子句,可为空,不带where
@orderStr   varchar(300),   --排序字段,可多个,要带desc或asc,不带order   by,必须,不能为空
--排序字段不能有空值,或者在where中排除空值或者用isnull函数解决

@needCound   bit   =   0,   --是否需要得到纪录总数
@pageIndex   int   =0,   --页索引
@pageSize   int=10,   --页大小
@recordCount   BIGINT   =0   output,   --返回纪录总数
@pageCount   int   =0   output   --返回页总数

as
declare   @sql   nvarchar(1300)   --主sql语句
declare   @orderStr2   varchar(300)   --order   by子句
set   @orderStr   =   LOWER(@orderStr)
set   @orderStr2   =   REPLACE(@orderStr, '   desc ', '   @a@ ')
set   @orderStr2   =   REPLACE(@orderStr2, '   asc ', '   @d@ ')
set   @orderStr2   =   REPLACE(@orderStr2, '   @a@ ', '   asc ')
set   @orderStr2   =   REPLACE(@orderStr2, '   @d@ ', '   desc ')
set   @orderStr   =   '   order   by   '   +   @orderStr
set   @orderStr2   =   '   order   by   '   +   @orderStr2

if(@whereStr   is   not   null   and   @whereStr   != ' '   )
set   @whereStr   =   '   where   '   +   @whereStr
else
set   @whereStr   =   ' '

if(@needCound   !=   0   or   @pageIndex   =   0)   --以下获得纪录总数
begin
DECLARE   @R   BIGINT
SET   @sql=   N 'select   @R=count(*)   from   '+@tbName
EXEC   SP_EXECUTESQL   @SQL,N '@R   BIGINT   OUTPUT ',@R   OUTPUT
SET   @recordCount   =   @R
set   @pageCount   =   ((@recordCount-1)/@pageSize)+1  
end

if(@pageIndex <2)   --如果是第一页
begin
set   @pageIndex   =   1
set   @sql= 'select   top   '+   str(@pageSize)   +   '   '+   @tbFields   +   '   from   '   +   @tbName   +   @whereStr   +   @orderStr;
end

else   --其它页
begin
SET   @sql=   'SELECT   '   +   @tbFields   +   '   FROM   ( '
+   'SELECT   TOP   '   +   STR(@pageSize)   +   '   '+   @tbFields   +   '   FROM   ( '
+   'select   top   '   +   STR(@pageSize*@pageIndex)   +   '   '+   @tbFields   +   '   FROM   '
+   @tbName   +   @whereStr   +   @orderStr   +   ')   as   a '
+   @orderStr2   +   ')   as   b '   +   @orderStr
end
print   @sql  
EXEC   SP_EXECUTESQL   @sql  

--测试
declare   @a   BIGINT,@b   BIGINT
exec   sp_pagination   'orders ', '* ', ' ', 'orderid   asc ',1,5,10,@a   output,@b   output
print   @a
print   @b
------------------------------------------------------------------------------------------------------

第二种方案:基于not   in   ,原型如下

select   top   页大小   *
from   testtable
where   (
id   not   in
(select   top   页大小*页数   id   from   表   order   by   id)
)
order   by   id

弊病:1   强制排序
2   排序列必须是唯一列,否则分页情况不符实际
3.   使用not   in,速度慢,

第三种方案:   基于max   或者   min   ,原型如下:

select   top   页大小   *
from   testtable
where   (
id   >   (  
select   max(id   )   from   (   select   top   页大小*页数   id   from   表
order   by   id   )   as   t
)
)  
order   by   id

弊病:1   强制排序
2   排序列必须是唯一列,否则分页情况不符实际

最后总结:
sqlserver   分页就是烂,第二第三种方案基本上是淘汰掉了
,因为现在基本上什么表都是根据添加时间来排序,所以那两种方案
没有用,真亏作者也敢发布出来,
只有第一种方案还是稍微能用一下,但还是要复杂的拼sql   语句,不方便,要通用于所有表有点难度,
象oracle   就很方便了,基于rownum,传入一个sql   查询语句,这个查询语句爱怎么写就怎么写,反正保证它得到一个结果集就行,不像sqlserver又是要求唯一健又是要求必须排序,把一个结果集颠来倒去,不慢才怪呢,

分享到:
评论

相关推荐

    关于SQL Server SQL语句查询分页数据的解决方案

    关于SQL Server SQL语句查询分页数据的解决方案 在日常工作中,我们经常需要处理大量数据,并从中获取特定页面的数据。特别是在Web应用开发中,为了提高用户体验,通常会将数据进行分页显示。针对这一需求,本文将...

    SQL Server数据库三种分页方案详尽分析

    **分页方案三:游标存储过程** 此方案使用SQL的游标来实现分页,通过创建一个存储过程来执行。这种方法适用于没有主键或无法使用其他方法的情况。然而,游标通常被认为是性能较差的选择,因为它们逐行处理数据,不...

    SQL Server 分页方案比拼

    在IT领域,尤其是在数据库管理与查询优化方面,SQL Server分页技术是数据检索中的一个关键环节。分页技术主要用于在大型数据库中实现高效的数据展示,它允许用户在不加载整个结果集的情况下,按需获取数据的特定部分...

    SQL Server 的通用分页显示存储过程

    一种改进后的分页存储过程采用了SQL Server中的表变量技术,示例代码如下: ```sql CREATE procedure pagination1(@pagesize int, -- 页面大小,如每页存储20条记录 @pageindex int) -- 当前页码 as set nocount ...

    SQL Server 2000 通用分页过程

    这个通用分页存储过程提供了一种灵活且高效的分页解决方案。通过合理的参数配置和关键字选择,可以有效地优化查询性能。在实际应用中,还需要根据具体场景调整参数设置,以达到最佳效果。此外,随着数据库技术的发展...

    最简单的SQL Server数据库存储过程分页

    在SQL Server中,通过存储过程实现分页是一种常见且高效的解决方案。其核心思想是利用游标(Cursor)或临时表来获取数据总量,并根据指定的页码和每页数量来计算出需要返回的具体记录范围。这种方法的优势在于它可以...

    海量数据分页sql server经典

    本文将介绍几种在SQL Server中实现分页的有效方法,并着重讨论一种基于SQL查询优化的分页技术。 #### 传统分页方法及其局限性 - **ADO 记录集分页法**:这种方法利用ADO的内置功能实现分页。但是它存在明显的缺点...

    SQL server 分页查询

    ### SQL Server分页查询概述 分页查询是指从大量数据中按需取出一部分数据的过程,通常用于网页展示或大型数据报表中,以提高加载速度和减少服务器负担。在SQL Server中,实现分页查询有多种方法,包括使用`ROW_...

    SQLServer数据库分页查询

    本文主要探讨在 SQL Server 2008 R2 版本中实现分页查询的五种不同方法,并对这些方法进行了性能测试。 #### 二、测试环境与条件 - **数据库**: SQL Server 2008 R2 - **表名**: ARTICLE - **字段**: ID (主键,...

    sqlserver 存储过程分页(支持千万级)算法研究

    第三种方法是使用MAX函数结合TOP语句来实现分页。这种方法的关键在于,通过递归地查找当前页之前的最大ID值,然后基于这个值进行筛选,获取当前页的数据。 #### 实现细节: 1. **计算前一页最大ID**:通过`SELECT ...

    在SQL Server中通过SQL语句实现分页查询

    通过对上述三种分页方案的分析可以看出,在实际应用中应根据具体情况选择合适的分页策略。如果表中有递增的唯一标识符(如ID),那么推荐使用方案二,因为它在大多数情况下提供更好的性能。若没有这样的唯一标识符,...

    jsp+sqlserver分页代码

    String strConn="jdbc:sqlserver://localhost:1433;DatabaseName=abc"; conn=DriverManager.getConnection(strConn,"sa","123"); ``` - **`Class.forName`**:加载SQL Server的JDBC驱动。 - **`String strConn`**:...

    SQL Server 存储过程的分页方案比拼

    本文将对比三种不同的SQL Server存储过程中的分页方案,以帮助你选择最适合你应用场景的方法。 ### 分页方案一:Not In + SELECT TOP 这种方法是通过`NOT IN`子句和`SELECT TOP`配合实现分页。首先,它获取前N个ID...

    Sql server 分页存储过程的性能对比

    在SQL Server数据库管理中,分页查询是一种常见的需求,特别是在数据量庞大的情况下,为了提高用户体验,需要快速加载部分数据而不是一次性加载所有数据。本篇资料主要探讨的是在SQL Server中,利用存储过程实现分页...

    Sql Server 各种数据分页

    在SQL Server中,数据分页是一种重要的查询策略,特别是在处理大量数据时,它能有效地提高查询性能并优化用户体验。分页允许用户逐步加载和查看数据,而不是一次性加载所有记录,这对大型数据库尤其必要。以下是对...

    SQL Server 存储过程的分页方案比拼.doc

    ### 分页方案三:利用SQL游标的存储过程分页 第三种方案采用存储过程结合游标的方式实现分页。游标允许逐行处理数据,这对于需要处理大量数据的应用程序来说,提供了更大的灵活性。然而,游标的使用通常伴随着较高...

    Sqlserver2005数据库级别异步分页

    综上所述,数据库级别的异步分页结合了SQL Server的存储过程和JavaScript的前端处理,为大型数据集提供了一种高效、流畅的分页解决方案。通过合理的设计和优化,可以在保证用户体验的同时,降低服务器负载,提升系统...

    SQL Server海量数据库的查询优化及分页算法方案

    在SQL Server中,面对海量数据的数据库,查询性能优化与分页算法的实施显得尤为重要。本文将深入探讨这两个关键知识点,旨在帮助数据库管理员和开发人员提高查询效率,改善用户体验。 一、SQL Server查询优化 1. *...

Global site tag (gtag.js) - Google Analytics