数据库分页

第一种方案: 两次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又是要求唯一健又是要求必须排序,把一个结果集颠来倒去,不慢才怪呢,