近段时间基于sqlServer2000进行了一些分页测试,在网上搜到的分页(非存储过程方式)方案中大部分都存在细节的完善,特将正确的细节补充如下:

网上搜到的分页实现如下:

//oracle
    public static String query(String sql,int page,int length){  
          if( page<=0 ) {   
            page = 1;   
          }  
          int begin  = (page-1)*length;   
          int end = page*length;   
          String qs =" SELECT * FROM ( SELECT row_.*,ROWNUM rownum_ FROM ( "   
                     + sql   
                     + " ) row_ WHERE ROWNUM <= " + end + ") WHERE rownum_ >" +begin;   
         return qs;   
      }

/**

     * 分页（sqlserver2000）      * 例： "SELECT * FROM  (SELECT TOP 10 * FROM  (SELECT TOP 10 id,name,age "+           " FROM user where 1=1 order by id desc ) TB2  order by id asc) TB3  order by id desc ";      * @param sqlFields 搜索字段      * @param tableName    表名      * @param where    where条件      * @param orderby    排序(写上desc，asc)      * @param page    当前页      * @param length    显示条数      * @return      */     public static String queryBysqlServer(String sqlFields,String tableName,String where,String orderby,int length){             if( page<=0 ) {                page = 1;              }             String cutorderby = "";           orderby = orderby.toLowerCase();           if(orderby.contains("desc")){               cutorderby = orderby.replace("desc","asc");           }else{               cutorderby = orderby.replace("asc","desc");           }           StringBuffer sb = new StringBuffer();           sb.append("SELECT * FROM (SELECT TOP ");           sb.append(length);           sb.append(" * FROM  (SELECT TOP ");           sb.append(page*length).append(" ");           sb.append(sqlFields).append(" ");           sb.append(" FROM ");           sb.append(tableName).append(" ");           sb.append(where).append(" ");           sb.append(orderby).append(" ");           sb.append(" ) TB2 ");           sb.append(cutorderby).append(" ");           sb.append(" ) TB3 ");           sb.append(orderby);           return sb.toString();                  }

好久没有用数据库了。最近在做项目时突然发现6年前自己的分页代码有问题的。

这个问题客户居然从来没反馈过。原来是通过主键来分页的，但排序条件加入后其实不能保证主键队列也是排序的，因此 ... WHERE ZID > MAX(...) ... 的做法不对的。

现在改进，使用 NOT IN 的方法。思路是：比如显示第3页，每页10条记录，那么取出30条记录，但是过滤掉前20条，这样排序是解决了，但效率应该降低了。

贴代码：

CREATE PROCEDURE sp_GetPageData
 @sSelect NVARCHAR(100),--select 子句
 @sFrom NVARCHAR(200),   --from 子句
 @sWhere NVARCHAR(200),  --where 子句
 @sOrder NVARCHAR(50),   --order by 子句
 @iCurPageNo int,        --当前页码
 @iPageSize int,         -- 分页大小
 @iRecordCount int out   --输出总数据条目数量  
  
AS

DECLARE @iPageCount int   --分页数量
DECLARE @sqlString NVARCHAR(800)        --总sql
DECLARE @ParmDeFinition NVARCHAR(50)    --参数定义
DECLARE @sWhereIn NVARCHAR(200)         --NOT IN 子句

 IF @iPageSize < 1

   SET @iPageSize = 10

--计算记录数目
SET @sqlString = N'SELECT @iRecordCount = COUNT(*)  FROM ' + @sFrom +  N' WHERE' + @sWhere
--+ ' ORDER BY ' + @sOrder

SET @ParmDeFinition = N'@iRecordCount int OUT'
print @sqlString

EXECUTE sp_executesql @sqlString,@ParmDeFinition,@iRecordCount OUT
print N'@iRecordCount = ' + CAST(@iRecordCount AS varchar)

if (0 = @iRecordCount)
begin
 print 'data is null'
 return
end

--计算分页
if (@iRecordCount % @iPageSize > 0)
  SET @iPageCount = @iRecordCount / @iPageSize + 1
ELSE
  SET @iPageCount = @iRecordCount / @iPageSize

--保证请求页在页码范围内
if (@iCurPageNo > @iPageCount)
 SET @iCurPageNo = @iPageCount
 
--返回数据
if (@iCurPageNo > 1)
BEGIN
  SET @sWhereIn = N' AND m.ZID NOT IN(SELECT TOP ' + CAST(@iPageSize * (@iCurPageNo - 1) AS VARCHAR) + N' m.ZID FROM ' + @sFrom +  N' WHERE' + @sWhere + ' ORDER BY ' + @sOrder + ')'
  SET @sqlString = N'SELECT TOP ' +  CAST(@iPageSize AS VARCHAR) + @sSelect + N' FROM ' + @sFrom +  N' WHERE' + @sWhere + @sWhereIn + ' ORDER BY ' + @sOrder
END
else  -- 第一页
   SET @sqlString = N'SELECT TOP ' +  CAST(@iPageSize AS VARCHAR(8)) + @sSelect + N' FROM ' + @sFrom +  N' WHERE' + @sWhere + ' ORDER BY ' + @sOrder
 
print @sqlString

exec (@sqlString)

select RecordCount = @iRecordCount

go

 

--测试输入

declare @iRecordCount int
exec sp_GetPageData
N' m.*,g.ZNAME GroupName ',
N'TB_USER m LEFT JOIN TB_GROUP g ON m.ZGROUP_ID = g.ZID ',
N' 1=1',
N' m.ZID DESC',
10,
1,
@iRecordCount out

print 'iRecordCount = ' + CAST(@iRecordCount AS VARCHAR)