基于SQL词法分析的多种数据库自动分页方案

基于SQL词法分析的多种数据库自动分页方案

(原文见 http://www.pwmis.cn/bbs/dispbbs.asp?boardID=8&ID=595 )

一、背景:
1,巨大的数据处理量;
2,快速分页;
3,动态查询(列表显示的项目);
4,适应多种数据库;
5,适应多表查询;
6,适应无主键或主键无序的情况;

二、算法:
一般快速分页都是采用基于有序主键采用二分查找排序的算法,可以应对大数据量的分页要求。
但是实际运用中往往需要对某些非主键字段排序分页,或者有些查询没有主键,或者主键是无序的。
本文采用两种分法来处理这几类情况:
(注意:下面的算法中均以降序为准,1,2 方法考虑在SQL SERVER 及ACCESS 中的实现方法)
1,有主键,并且主键是有序的:
SELECT TOP  @@PageSize * FROM
    (SELECT TOP @@PageSize * FROM 
      (SELECT TOP  @@Page_Size_Number [@@FieldList]
        FROM (@@DataSourceTable) st
          @@Where
          ORDER BY @@PrimaryKey DESC
      ) t1 ORDER BY @@PrimaryKey ASC
    ) t2 ORDER BY @@PrimaryKey DESC

说明:在查询中,使用主键排序比采用其它字段排序拥有极高的效率。
[@@DataSourceTable] 可能是一个单表的查询,例如:

SELECT * FROM USERINFO WHERE USERCLASS='1' ORDER BY USERNAME DESC

也可能是一个复杂的多表查询,例如:

SELECT A.ArticleID, A.ClassID, A.Title, A.CreateTime, A.Hints, A.Writer ,A.PaperDate,A.FromOffice,C.ClassName AS ClassName
FROM TB_OA_Article A
INNER JOIN  TB_OA_Class C ON A.ClassID = C.ClassID 

或者是一个视图,例如:

SELECT * FROM VIEW1

[@@Where] 表示要多数据源的筛选条件。
注:把数据筛选条件放到这一层次可以避免 [@@DataSourceTable] 中有复杂的多表查询的情况引起“字段不明确”的问题。

***以下如果没有特殊说明,出现的相同单词均以此处为准。***


2,有主键,同时主键可能是无序的;或者没有主键:

  SELECT TOP  @@PageSize * FROM
    (SELECT TOP @@PageSize * FROM 
      (SELECT TOP  @@Page_Size_Number [@@FieldList]
        FROM (@@DataSourceTable) st
          @@Where
          ORDER BY @@OrderField DESC
      ) t1 ORDER BY @@OrderField ASC
    ) t2 ORDER BY @@OrderField DESC

说明:
该方式可以适应 各种分页及排序的要求。
[@@OrderField] 可以不是主键,可以有多个,但是必须分别指明排序方式 [desc]/[asc]

3,Oracle 分页算法:
select [@@FieldList] from (
   select [@@FieldList],rownum rn from
    (SELECT [@@FieldList] FROM ( @@DataSourceTable ) ST0 @@Where order by @@OrderField DESC ) st
    where rownum <= @@Page_Size_Number
)
where rn >= (@@Page_Size_Number-@@PageSize)
说明:
在Oracle中,由于虚拟列 rownum 实在排序之后输出的,所以必须采用嵌套的查询来获取制定范围内的数据。


三、实现方案:
根据上面的算法,可以发现它们构造的SQL语句及其相似,算法1其实就是算法2的特例,在实现本文的分页方案的时候,
只需要判断是否提供了主键即可。
另外,为了尽可能提高效率,在查询第一页和最后一页的时候,需要分别处理(ORACLE 不需要下面的处理):
1,查询第一页:
SELECT TOP @@PageSize [@@FieldList]
  FROM   (@@DataSourceTable) st
    @@Where
    ORDER BY @@OrderField DESC
说明:对于第一页,直接采用Top 方式,即可以拥有很高的效率。

2,查询最后一页:
SELECT * FROM
  (SELECT Top @@LeftSize [@@FieldList]
    FROM (@@DataSourceTable) st
       @@Where
       ORDER BY @@OrderField ASC
  ) T1 ORDER BY @@OrderField DESC

说明:对于最后一页,只需要第一次取数据的时候倒序,第二次正序即可。注意这里,@@LeftSize 的大小,
可能小于 @@PageSize,需要计算它的大小,这样,在处理所有的分页的时候,涉及到一个计算最后一页大小的问题。
假设 符合条件的所有记录数量为 AllCount,每一页的大小为 PageSize,当前页码为 PageNumber,
那么计算最后一页大小的方法如下:

 if(PageSize*PageNumber>AllCount) //最后的页 @@LeftSize
    LeftSize=AllCount-PageSize*(PageNumber-1);
 else
    LeftSize=PageSize;

取得符合条件的记录数的查询:

SELECT COUNT(*) FROM (@@DataSourceTable) st
    @@Where
说明:统计记录数不再需要 Order by 子句。如果有主键,也可以采用下面的语句提高效率:

SELECT COUNT(@PrimaryKey) FROM (@@DataSourceTable) st
    @@Where
-----------------------------------------------------------------

好了,上面已经分析了各种情况下处理分页及排序的最佳算法,现在涉及到真正实现上面算法的时候了,
根据上面的算法,我们可以正确地写出适应不同情况的分页排序SQL 语句,但是做一次分页排序需要写
4条SQL语句,在一个项目中这样的SQL语句将会多得吓人(我们目前的一个方案就是采用每次查询分页手
写四条SQL的方式),有没有更好的办法来解决这个问题呢?

[解决方案]
******************采用 SQL 词法分析 ************************

一提到“词法分析” 有点像编译语言的词法分析了,其实在每一个SQL语句中,不管查询有多么复杂,
都可以分为以下4部分,而且是顺序铁定的4部分:

SELECT ... FROM ... [WHERE ...] [ORDER BY ...]

其中,Where 子句和 Order by 子句都是可选的,而 Select 子句中的某一个字段可以是一个子查询,
From 子句 可以是一个或者多个表,甚至是一个( ) 中的子查询。但是根据 SQL 语句中的这几部分
特别的语法顺序,我们自动分离出每一个子句是不成问题的,这里只要注意 带 ( ) 的子查询就是了。
所以,在我们需要对一次查询作分页以及排序的时候,我们只需要写出上面的标准SQL 语句即可,然后
程序可以取得我们要筛选的条件,待排序的字段及方式,自动地完成符合特定数据库的分页查询语句。
 
在用户给出的 一条查询 SQL 中,我们可以简单的对其进行字符串处理,就可以把各个子句提取出来,
然后根据上面的算法,构造出与具体数据库无关分页查询语句。

**********************特别说明**********************************************
在最终实现的时候,用户给的SQL 语句,其实就是上面的 “@@DataSourceTable”,之所以要这么处理,
就是因为用户的这个SQL 语句,可能是一个简单的单表查询,可以能是一个复杂的多表连接查询,甚至
是一个存储过程(如果是存储过程的话,那么需要手工指明需要返回那些字段以及待排序的字段)
*****************************************************************************

ok,下面我们来进行第一步,“ SQL 词法分析”
(2006.5.29 ,未完待续)

 

我们先来看几个典型的查询语句:

1,简单的单表查询:
SELECT UserID,UserName,UserClass FROM USERINFO WHERE USERCLASS='1' ORDER BY USERNAME DESC
分析:对该表进行简单的单词查找,很容易取得Select ,From ,Where ,Order by 子句。
经过转换,将构造如下语句:
SELECT TOP  @@PageSize * FROM
    (SELECT TOP @@PageSize * FROM 
      (SELECT TOP  @@Page_Size_Number UserID,UserName,UserClass
        FROM USERINFO
          WHERE USERCLASS='1'
          ORDER BY USERNAME DESC
      ) t1 ORDER BY USERNAME ASC
    ) t2 ORDER BY USERNAME DESC

注意:为了以后有可能将该查询结果绑定到数据表格,然后通过模糊查找表格中显示的文字内容(不限列),
有必要加上 @@Where 替换参数(指在运行时用文本方式替换重新生成一条查询语句),上面的查询将变成:

SELECT TOP  @@PageSize * FROM
    (SELECT TOP @@PageSize * FROM 
      (SELECT TOP  @@Page_Size_Number * FROM
        (SELECT UserID,UserName,UserClass
          FROM USERINFO
          WHERE USERCLASS='1'
        ) ST
        @@Where ORDER BY USERNAME DESC
      ) t1 ORDER BY USERNAME ASC
    ) t2 ORDER BY USERNAME DESC

注意:@@Where ORDER BY USERNAME DESC 中的 Order by 子句,放在最内的一层原是查询语句的外面了,
为什么要这样做呢?在SQL SERVER 中,“除非指定了Top谓词,否则在嵌套的查询语句中不允许使用
Order by 子句”!所以对原始SQL 语句中的排序条件的处理就很重要了,对于单表的查询还比较容易处理,
但是对于复杂的查询就比较困难了,接着看下面的情况。

2,复杂的多表连接查询:
select
B.BussinessID,B.ApplyCompany,B.Address,B.AddressNo,
      M.MPID,M.Creator,M.CreateDate as PlanCreateDate,M.State,
(select Top 1 UserName from TB_Common_Uesr where UserID=M.Creator order by userid desc) as CreatorName
   FROM   TB_Flow_Bussiness B
       Left join TB_Flow_MeasurePlan M on B.BussinessID=M.BussinessID
WHERE  M.State='01'
ORDER BY PlanCreateDate DESC

注意:在子查询
select Top 1 UserName from TB_Common_Uesr where UserID=M.Creator order by userid desc
中,使用了order by 谓词,在主查询中,也使用了order by 谓词,所以不能够简单的查找
SELECT,FROM,WHERE,ORDER BY 谓词的位置来确定相应的子句内容。
实际上,我们关心的只是最外层的 ORDER BY 子句,通过确定的查询谓词的顺序,我们很容易找到该
 ORDER BY 子句。经过转换,将生成如下的语句:

SELECT Top 10 * from
 (select B.BussinessID,B.ApplyCompany,B.Address,B.AddressNo,
      M.MPID,M.Creator,M.CreateDate as PlanCreateDate,M.State,
      (select Top 1 UserName from TB_Common_Uesr where UserID=M.Creator order by userid desc) as CreatorName
   FROM   TB_Flow_Bussiness B
       Left join TB_Flow_MeasurePlan M on B.BussinessID=M.BussinessID
   WHERE  M.State='01' 
) ST
where bussinessid like '%2006%' and CreatorName='XIAO'
ORDER BY PlanCreateDate DESC

如果最外层的Order by 子句中的 排序字段包含 “数据表.字段名” 这样的限定标记,应该替换只有字段名
的标记,例如上面如果还以 M.MPID 排序,那么在外层的排序语句中,应该使用 MPID 的形式。

3,从另外的查询结果集中进行的查询:
指的是如下的查询方式:
SELECT [@@FieldList]
    FROM (@@DataSourceTable) st
       Where @@WhereStr
         ORDER BY @@OrderField ASC

其中,@@DataSourceTable 是另外一个查询结果集,整个查询的实例可能如下:

SELECT * from
 (select B.BussinessID,B.ApplyCompany,B.Address,B.AddressNo,
      M.MPID,M.Creator,M.CreateDate as PlanCreateDate,M.State,
      (select Top 1 UserName from TB_Common_Uesr where UserID=M.Creator order by userid desc) as CreatorName
   FROM   TB_Flow_Bussiness B
       Left join TB_Flow_MeasurePlan M on B.BussinessID=M.BussinessID
   WHERE  M.State='01' 
) ST
where bussinessid like '%2006%' and CreatorName='XIAO'
ORDER BY PlanCreateDate DESC

这样我们不管另外的查询结果集有多么复杂,都可以归结到上面的公式中

4,从“存储过程结果集”中进行的查询:
在SQL2000以上,支持一种特殊的存储过程--用户自定义函数,它可以表的形式返回一个查询结果集,
这样我们就能 从“存储过程结果集”中进行的查询了,请看下面的例子:

--从函数中选择结果集示例:
CREATE FUNCTION FUN_TEST1
(@STATE VARCHAR(10))
RETURNS TABLE --返回表类型
AS
RETURN
(SELECT B.BussinessID,B.ApplyCompany,B.Address,B.AddressNo,
      M.MPID,M.Creator,M.CreateDate as PlanCreateDate,M.State
   FROM   TB_Flow_Bussiness B
       Left join TB_Flow_MeasurePlan M on B.BussinessID=M.BussinessID
   WHERE  M.State=@STATE
)

GO
--从函数中选择结果集
SELECT BussinessID,AddressNo,PlanCreateDate
  FROM DBO.FUN_TEST1 ('01')
    ORDER BY MPID
GO

看到了吗?我们的From 子句中,可以包含一个用户自定义函数,这样我们就可以把很复杂的查询写在
SQLSERVER的用户自定义函数中,在实际的使用的时候就很简单了:)
由于从“存储过程结果集”中进行的查询SQL语句比较简单,分析处理比较容易,用它构建分页就很容易了.
(既然查询都写到了用户函数中了,干吗不直接在存储过程中分页?我们这里只是提供这么一种可能.)

总结:
不管对于各种不同的查询,只要我们能够正确地提取Select ,From ,Where ,Order by 子句,我们就能够
构建出本文所述的分页排序语句来,从而将目前复杂的分页问题减轻到最低程度.

到现在为止,我们可以进行实战了,"编程分析SQL语句"!
(2006.5.30 ,未完待续)
* 函数 MakeSQLStringByPage 在此基础上实现了更为复杂的分页处理,这里的复杂时说查询
   * 包含大量的子查询或者连接查询,因此评价查询复杂与否采用下面的标准:
   *
   * 只包含一个 SELECT 谓词;
   * 没有 INNER JOIN,RIGHT JOIN,LEFT JOIN 等表连接谓词;
   * 谓词 FROM 后只能有一个表名;
   *
   * 否则,视为该查询为一个复杂查询,采用复杂查询分页方案;
  

** 约束:
使用该分页方法要求 SQL语句本身必须满足下列条件:
1,最外层的查询不能含有 TOP 谓词;
2,最外层查询必须含有 ORDER BY 语句;
3,不能含有下列替换参数(区分大小写):@@PageSize,@@Page_Size_Number,@@LeftSize,@@Where
4,SQL必须符合 SQL-92 以上标准,且 最外层ORDER BY 语句之后不能有其他语句,
Group by 等放在Order by 之前;
**
   */

/// <summary>
 /// SQL SERVER 分页处理,自动识别标准SQL语句并生成适合分页的SQL语句
 /// </summary>
 public class SQLPage
 {
  public SQLPage()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  /// <summary>
  /// 生成SQL分页语句,记录总数为0表示生成统计语句
  /// </summary>
  /// <param name="strSQLInfo">原始SQL语句</param>
  /// <param name="strWhere">在分页前要替换的字符串,用于分页前的筛选</param>
  /// <param name="PageSize">页大小</param>
  /// <param name="PageNumber">页码</param>
  /// <param name="AllCount">记录总数</param>
  /// <returns>生成SQL分页语句</returns>
  public static string MakeSQLStringByPage(string strSQLInfo,string strWhere,int PageSize,int PageNumber,int AllCount)
  {
   string strSQLType=string.Empty ;
   if(AllCount!=0)
   {
    if(PageNumber==1) //首页
    {
     strSQLType="First";
    }
    else if(PageSize*PageNumber>AllCount) //最后的页 @@LeftSize
    {
     PageSize=AllCount-PageSize*(PageNumber-1);
     strSQLType="Last";
    }
    else //中间页
    {
     strSQLType="Mid";
    }
   }
   else //特殊处理
   {
    strSQLType="Count";
   }
   string SQL=MakeSQLStringByPage( strSQLInfo, strSQLType);
   //执行分页参数替换
   SQL=SQL.Replace ("@@PageSize",PageSize.ToString ())
    .Replace ("@@Page_Size_Number",Convert.ToString (PageSize * PageNumber))
    .Replace ("@@LeftSize",PageSize.ToString ())
    .Replace ("@@Where",strWhere);
   return SQL;

  }

  private static string MakeSQLStringByPage(string strSQLInfo,string strSQLType)
  {
   #region SQL 复杂度分析
   //SQL 复杂度分析 开始
   bool SqlFlag=true;//简单SQL标记
   string TestSQL=strSQLInfo.ToUpper ();
   int n=TestSQL.IndexOf ("SELECT ",0);
   n=TestSQL.IndexOf ("SELECT ",n+7);
   if(n==-1)
   {
    //可能是简单的查询,再次处理
    n=TestSQL.IndexOf (" JOIN ",n+7);
    if(n!=-1) SqlFlag=false;
    else
    {
     //判断From 谓词情况
     n=TestSQL.IndexOf("FROM ",9);
     if(n==-1) return "";
     //计算 WHERE 谓词的位置
     int m=TestSQL.IndexOf ("WHERE ",n+5);
     // 如果没有WHERE 谓词
     if(m==-1) m=TestSQL.IndexOf ("ORDER BY ",n+5);
     //如果没有ORDER BY 谓词,那么无法排序,退出;
     if(m==-1) return "";
     string strTableName=TestSQL.Substring (n,m-n);
     //表名中有 , 号表示是多表查询
     if(strTableName.IndexOf (",")!=-1)
      SqlFlag=false;
    }
   }
   else
   {
    //有子查询;
    SqlFlag=false;
   }
   //SQL 复杂度分析 结束
   #endregion

   #region 排序语法分析
   //排序语法分析 开始
   int iOrderAt=strSQLInfo.ToLower ().LastIndexOf ("order by ");
   //如果没有ORDER BY 谓词,那么无法排序分页,退出;
   if(iOrderAt==-1) return "";
   string strOrder=strSQLInfo.Substring (iOrderAt+9);
   strSQLInfo=strSQLInfo.Substring(0,iOrderAt);
   string[] strArrOrder=strOrder.Split (new char []{','});
   for(int i=0;i<strArrOrder.Length ;i++)
   {
    string[] strArrTemp=(strArrOrder[i].Trim ()+" ").Split (new char[]{' '});
    //压缩多余空格
    for(int j=1;j<strArrTemp.Length ;j++)
    {
     if(strArrTemp[j].Trim ()=="")
     {
      continue;
     }
     else
     {
      strArrTemp[1]=strArrTemp[j];
      if(j >1 ) strArrTemp[j]="";
      break;
     }
    }
    //判断字段的排序类型
    switch(strArrTemp[1].Trim ().ToUpper ())
    {
     case "DESC":
      strArrTemp[1]="ASC";
      break;
     case "ASC":
      strArrTemp[1]="DESC";
      break;
     default:
      //未指定排序类型,默认为降序
      strArrTemp[1]="DESC";
      break;
    }
    //消除排序字段对象限定符
    if(strArrTemp[0].IndexOf (".")!=-1)
     strArrTemp[0]=strArrTemp[0].Substring (strArrTemp[0].IndexOf (".")+1);
    strArrOrder[i]=string.Join (" ",strArrTemp);

   }
   //生成反向排序语句
   string strNewOrder=string.Join (",",strArrOrder).Trim ();
   strOrder=strNewOrder.Replace ("ASC","ASC0").Replace ("DESC","ASC").Replace ("ASC0","DESC");
   //排序语法分析结束
   #endregion

   #region 构造分页查询
   string SQL=string.Empty ;
   if(!SqlFlag)
   {
    //复杂查询处理
    switch(strSQLType.ToUpper ())
    {
     case "FIRST":
      SQL="Select Top @@PageSize * FROM (/n" +strSQLInfo+
       "/n) T0 @@Where ORDER BY "+strOrder;
      break;
     case "MID":
      SQL=@"SELECT Top @@PageSize * FROM
                (SELECT Top @@PageSize * FROM
               (
                             SELECT Top @@Page_Size_Number * FROM (";
      SQL+="/n"+strSQLInfo+" ) P_T0 @@Where ORDER BY "+strOrder+"/n";
      SQL+=@") P_T1
   ORDER BY "+ strNewOrder +") P_T2 /n"+
       "ORDER BY "+strOrder;
      break;
     case "LAST":
      SQL=@"SELECT * FROM ( 
                       Select Top @@LeftSize * FROM ("+"/n/r"+strSQLInfo+"/r";
      SQL+=" ) P_T0 @@Where ORDER BY "+ strNewOrder+"/n/r"+
       " ) P_T1 ORDER BY "+strOrder;
      break;
     case "COUNT":
      SQL="Select COUNT(*) FROM ( " +strSQLInfo+" ) P_Count @@Where";
      break;
     default:
      SQL=strSQLInfo+strOrder;//还原
      break;
    }

   }
   else
   {
    //简单查询处理
    switch(strSQLType.ToUpper ())
    {
     case "FIRST":
      SQL=strSQLInfo.ToUpper().Replace ("SELECT ","SELECT TOP @@PageSize ");
      SQL+="  @@Where ORDER BY "+strOrder;
      break;
     case "MID":
      string strRep=@"SELECT Top @@PageSize * FROM
                (SELECT Top @@PageSize * FROM
               (
                             SELECT Top @@Page_Size_Number  ";
      SQL=strSQLInfo.ToUpper().Replace ("SELECT ",strRep);
      SQL+="  @@Where ORDER BY "+strOrder;
      SQL+=" /r/n) P_T0 ORDER BY "+ strNewOrder+"/n/r"+
       " ) P_T1 ORDER BY "+strOrder;
      break;
     case "LAST":
      string strRep2=@"SELECT * FROM ( 
                       Select Top @@LeftSize ";
      SQL=strSQLInfo.ToUpper().Replace ("SELECT ",strRep2);
      SQL+=" @@Where ORDER BY "+ strNewOrder+"/n/r"+
       " ) P_T1 ORDER BY "+strOrder;
      break;
     case "COUNT":
      SQL="Select COUNT(*) FROM ( " +strSQLInfo+" ) P_Count @@Where";
      break;
     default:
      SQL=strSQLInfo+strOrder;//还原
      break;
    }
   }
   return SQL;
   #endregion
  }
 }Oracle分页算法

Oracle :
基本的分页原理利用Oracle内指的 rownum 伪列,它是一个递增序列,但是它在Order by 之前生成,通常
采用下面的分页语句:
select * from
 (select rownum r_n,temptable.* from 
   ( @@SourceSQL ) temptable
 ) temptable2 where r_n between @@RecStart  and @@RecEnd
其中:
@@SourceSQL :当前任意复杂的SQL语句
@@RecStart:记录开始的点,等于 ((tCurPage -1) * tPageSize +1)
@@RecEnd  :记录结束的点,等于 (tCurPage * tPageSize)


  /// <summary>
  /// Oracle 分页SQL语句生成器
  /// </summary>
  /// <param name="strSQLInfo">原始SQL语句</param>
  /// <param name="strWhere">在分页前要替换的字符串,用于分页前的筛选</param>
  /// <param name="PageSize">页大小</param>
  /// <param name="PageNumber">页码</param>
  /// <param name="AllCount">记录总数</param>
  /// <returns>生成SQL分页语句</returns>
  private static string MakePageSQLStringByOracle(string strSQLInfo,string strWhere,int PageSize,int PageNumber,int AllCount)
  {
   if(AllCount==0)
   {
    //生成统计语句 
    return "select count(*) from ("+strSQLInfo+") ";
   }
   //分页摸板语句
   string SqlTemplate=@"SELECT * FROM
 (SELECT rownum r_n,temptable.* FROM 
   ( @@SourceSQL ) temptable @@Where
 ) temptable2 WHERE r_n BETWEEN @@RecStart  AND  @@RecEnd";

   int iRecStart= (PageNumber -1) * PageSize +1 ;
   int iRecEnd  = PageNumber * PageSize ;
   //执行参数替换
   string SQL=SqlTemplate.Replace ("@@SourceSQL",strSQLInfo)
    .Replace ("@@Where",strWhere)
    .Replace ("@@RecStart",iRecStart.ToString ())
    .Replace ("@@RecEnd",iRecEnd.ToString ());
   return strSQLInfo;
  }

没有更多推荐了,返回首页