posts - 0, comments - 77, trackbacks - 0, articles - 356
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

ACCESS中的分页解决方案

Posted on 2007-04-24 14:11 semovy 阅读(439) 评论(0)  编辑  收藏 所属分类: C++ Builder相关
String TUtilForm::getSQL(int recordCount ,int pageCount, int pageSize,int pageIndex,String tableName,String queryFields,String primaryKey,String ascending,String condition)
{
    int middleIndex = pageCount/2;
    int firstIndex = 0;
    int lastIndex = pageCount - 1;
    String SQLStr = "";
    if( pageIndex <= firstIndex )
    {
        SQLStr = "select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName;
        if( condition != "")
        SQLStr += " where " + condition ;
        SQLStr += " order by " + primaryKey + " " + ascending;
    }
    else if( pageIndex > firstIndex && pageIndex <= middleIndex )
    {
        SQLStr = "select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName + " where " + primaryKey ;
        if( ascending == "asc" )
        SQLStr +=  ">(select max(";
        else
        SQLStr += "<(select min(";
        SQLStr += primaryKey + ") from (select top " + IntToStr(pageSize*pageIndex) + " " + primaryKey + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " " + ascending + ") TableA )";
        if( condition != "" )
        SQLStr += " and " + condition;
        SQLStr += " order by " + primaryKey + " " + ascending;
    }
    else if( pageIndex > middleIndex && pageIndex < lastIndex)
    {
        SQLStr = "select " + queryFields + " from (select top " + IntToStr(pageSize) + " " + queryFields + " from " + tableName;
        SQLStr += " where " + primaryKey ;
        if( ascending == "asc" )
        SQLStr += " < ( select min(";
        else
        SQLStr += " > ( select max(";
        SQLStr += primaryKey + ") from (select top " +  IntToStr( recordCount - pageSize * (pageIndex + 1)) + " " + primaryKey + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " desc) TableA)" ;
        if( condition != "" )
        SQLStr += " and " + condition;
        SQLStr += " order by " + primaryKey + " desc) TableB order by )" + primaryKey + " " + ascending ;  
    }
    else
    {
        SQLStr = "select " + queryFields + " from (select top " + IntToStr(recordCount - pageSize*lastIndex) + " " + queryFields + " from " + tableName;
        if( condition != "" )
        SQLStr += " where " + condition;
        SQLStr += " order by " + primaryKey + " desc " + " )TableA order by " + primaryKey + " " + ascending;
    }
    return SQLStr;
}

只有注册用户登录后才能发表评论。


网站导航: