第一种方案:
两次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 Server 存储过程的分页方案比拼
SQL Server 海量数据库的查询优化及分页算法方案 SQL Server 海量数据库的查询优化及分页算法方案是非常重要的,因为在公安信息化的高速发展中,数据库的规模越来越大,查询和分页的速度变得越来越慢。因此,如何...
SQL Server 存储过程的分页方案比拼.doc
《SQL Server海量数据库的查询优化及分页算法方案》,忘了从哪里搞来的。
com.microsoft.sqlserver.jdbc.SQLServerException: 只进结果集不支持请求的操作 解决方案
几种sqlserver2008高效分页sql查询语句 top方案: sql code: select top 10 * from table1 where id not in(select top 开始的位置 id from table1) max: sql code: select top 10 * from table1 whe
Sqlserver数据库分页查询一直是Sqlserver的短板,分页查询每页30条,查询第1500页(即第45001-45030条数据)进行测试,分析出最高效的查询,感兴趣的朋友可以参考下哈
VS2005+SQL SERVER 2000+AspNetPager1 分页存储,终极解决方案
微软SQL Server 2016制作报表工具,提供了一组内部部署工具和服务,可用于建立、部署及管理行动与分页报表。SSRS 解决方案会以弹性方式提供正确的信息给需要的用户。 用户可以透过下列方式取用报表:网页浏览器、...
行式引擎按页取数只适用于Oracle,mysql,hsql和sqlserver2008及以上数据库,其他数据库,如access,sqlserver2005,sqlite等必须编写分页SQL。今天我们以Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的...
详细探讨如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数据分页!
本书全面系统地介绍了SQL Server开发和管理的应用技术,涉及安装和配置SQL Server、日期处理、字符处理、排序规则、编号处理、数据统计与汇总、分页处理、树形数据处理、数据导入与导出、作业、数据备份与还原、用户...
SQL中分页解决方案 涉及主流的DB Oracle、SQL Server、Mysql
本文我们主要对SQL Server多表查询的优化方案进行了总结,并给出了实际的例子进行性能与效率的对比,需要的朋友可以参考下
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...