基于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 子句中，可以包含一个用户自定义函数，这样我们就可以把很复杂的查询写在
ＳＱＬＳＥＲＶＥＲ的用户自定义函数中，在实际的使用的时候就很简单了：）
由于从“存储过程结果集”中进行的查询ＳＱＬ语句比较简单，分析处理比较容易，用它构建分页就很容易了．
（既然查询都写到了用户函数中了，干吗不直接在存储过程中分页？我们这里只是提供这么一种可能．）

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

到现在为止，我们可以进行实战了，＂编程分析ＳＱＬ语句＂！
（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;
  }