Posted on 2007-04-24 14:11
semovy 閱讀(445)
評論(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;
}