基于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分页算法