求教:如何分解sql语句 - Office中国论坛/Access中国论坛

Dim astrSQLNew() As String, strAlias As String, strTagOrder As String '新SQLL语句数组; 别名; 排序标记符号
'字段名称; 字段全名(含表名或为AS前一个字符串); 输入名称(显示名称)
Dim strField As String, strFieldSort As String, strInputName As String
Dim strFROMBefore As String, strFROMAfter As String, blnSorted As Boolean
Dim i As Integer, p As Integer, strFieldFull As String
Const conASC As String = "▲", conDESC As String = "▼" '升序标识符; 降序标识符
'1) 处理排序符号
strInputName = SortName
If TagSortField Then
strTagOrder = conASC
If Right(SortName, Len(conASC)) = conASC Then '末尾为升序,则转为降序
strInputName = Left(SortName, Len(SortName) - Len(conASC))
strTagOrder = conDESC
ElseIf Right(SortName, Len(conDESC)) = conDESC Then '末尾为降序,则转为升序
strInputName = Left(SortName, Len(SortName) - Len(conDESC))
strTagOrder = conASC
End If
End If
'2) 处理 SQL语句(分解成两部分——From之前的语句(不包括Select),From之后的语句(含From))
i = 1
Do Until i = p '将中间连续两个空格替换成一个空格
i = Len(SQLStatement)
SQLStatement = Replace(SQLStatement, " ", " ")
p = Len(SQLStatement)
Loop
SQLStatement = Trim(SQLStatement) '去除两头的空格
If Right(SQLStatement, 1) = ";" Then SQLStatement = Left(SQLStatement, Len(SQLStatement) - 1) '去掉分号
p = InStr(1, SQLStatement, " FROM ")
If p = 0 Or Left(SQLStatement, 7) <> "SELECT " Then Err.Raise 5: Exit Function
strFROMBefore = Left(SQLStatement, p - 1)
strFROMBefore = Mid(strFROMBefore, 7) '排除:SELECT
strFROMAfter = Mid(SQLStatement, p + 1)
'3) 对strFROMBefore字段部分语句进行处理(加入新排序标记)
astrSQLNew = Split(strFROMBefore, ",")
For i = 0 To UBound(astrSQLNew)
astrSQLNew(i) = Trim(astrSQLNew(i))
strFieldFull = astrSQLNew(i)
p = InStr(1, strFieldFull, " As ")
If p Then '3.1) 大于0,处理字段名与别名组合的语句
strAlias = Mid(strFieldFull, p + 4) '获得别名
strFieldFull = Left(strFieldFull, p - 1): strField = strFieldFull '获得字段名
'如已经处理排序,则只对别名字段作排序符号处理
If blnSorted Then
strAlias = AliasProcess(strAlias, TagSortField)
GoTo SorFieldProcess:
Else
'判断字段名是否为排序名称
p = InStr(1, strFieldFull, ".") '从strFieldFull中提取strField
If p Then strField = Mid(strFieldFull, p + 1)
If strField = strInputName Then '如为排序字段,则更新语句(加入排序标记)
astrSQLNew(i) = strFieldFull & " AS " & strField & strTagOrder
blnSorted = True: strFieldSort = strFieldFull
Else
'判断别名是否为排序名称
strAlias = AliasProcess(strAlias, TagSortField)
If strAlias = strInputName Then '如为排序字段,则更新语句(加入排序标记)
strAlias = strAlias & strTagOrder
p = InStr(1, strAlias, " "): If p Then strAlias = "[" & strAlias & "]" '如有空格,则加中括号
astrSQLNew(i) = strFieldFull & " AS " & strAlias
blnSorted = True: strFieldSort = strFieldFull
Else '其他别名(不是排序名称),语句复位(去掉排序标记)
SorFieldProcess:
If strAlias = strField Then
astrSQLNew(i) = strFieldFull
Else
p = InStr(1, strAlias, " "): If p Then strAlias = "[" & strAlias & "]" '如有空格,则加中括号
astrSQLNew(i) = strFieldFull & " AS " & strAlias
End If
End If
End If
End If
ElseIf Not blnSorted Then '3.2) 字段处理
p = InStr(1, strFieldFull, ".") '从strFieldFull中提取strField
If p Then strField = Mid(strFieldFull, p + 1)
If strField = strInputName Then
astrSQLNew(i) = strFieldFull & " AS " & strField & strTagOrder
blnSorted = True: strFieldSort = strFieldFull
ElseIf strFieldFull = strInputName Then
astrSQLNew(i) = strFieldFull & " AS " & strFieldFull & strTagOrder
blnSorted = True: strFieldSort = strFieldFull
ElseIf (Right(strFieldFull, 1) = "*") Or (Left(strFieldFull, 4) = "All") Or (strFieldFull = "*") Then '特别处理理保留字"*"，没有排序符号的排序
strFieldSort = strInputName '暂定为排序字段
End If
'其他字段不做处理
End If
Next
'4) 组合成新的语句
strFROMBefore = "SELECT " & Join(astrSQLNew, ",")
p = InStr(1, strFROMAfter, " Order By ") '对strFROMAfter中排序作更新
If p Then strFROMAfter = Left(strFROMAfter, p - 1)
strFROMAfter = strFROMAfter & " Order By " & strFieldSort
If strTagOrder = conDESC Then strFROMAfter = strFROMAfter & " DESC" '如果为降序,则加入降序标识符
ModifySQLSortField = strFROMBefore & " " & strFROMAfter
End Function
'Purpose: 对别名的排序符号处理
Private Function AliasProcess(ByVal Alias As String, ByVal TagSortField As Boolean) As String
Const conASC As String = "▲", conDESC As String = "▼" '升序标识符; 降序标识符
If Left(Alias, 1) = "[" Then Alias = Mid(Alias, 2, Len(Alias) - 2) '排除两边的"[]"
If TagSortField And Right(Alias, Len(conASC)) = conASC Then '如别名含排序标记,则将删除
Alias = Left(Alias, Len(Alias) - Len(conASC))
ElseIf TagSortField And Right(Alias, Len(conDESC)) = conDESC Then
Alias = Left(Alias, Len(Alias) - Len(conDESC))
End If
AliasProcess = Alias
End Function

复制代码

'--------------------------------------------------
'Function: ModifySQLSortField V2.1
'Purpose: 从SQL语句中获得字段名称及别名
'Input: 输入参数
' [in] SQLStatement:[String]选择SQL语句("SELECT"语句)
' [in] SortName:[String]需要排序字段名称或者别名
' [in] TagSortField:[Boolean]在名称中是否包含排序标记，True:包含，Fasle:不包含
'Output: 成功返回修改排序字段后SQL字句
'Refer: 调用过程:AliasProcess
'PseudoCode:示例代码(伪代码)
' 1. ListBox.RowSource = ModifySQLSortField(ListBox.RowSource, strFieldName, True) 'LB单击事件中,排序列表框,名称中有标记
' 2. ListBox.RowSource = ModifySQLSortField(mRowSource, strFieldName,False) 'LB单击事件中,排序列表框,名称中没有标记
'Infor: SQLStatement只能为选择SQL语句("SELECT"语句)(也不能为 SELECT...INTO 语句); TagSortField 一般都为 True
'不能处理含有关键字
' 编写:坚果 2006/01/20 说明:创建修改:2006/02/25-2006/04/14 说明:经过四次修改,但代码还是冗长(160行之多)
' 修改:2006/05/15 说明:全部重新编写代码,原V1.0中对整个语句进行处理.现对代码语句拆分成两部分处理.代码精简. 修改:2006/05/15 说明:Bug修复
'--------------------------------------------------
Public Function ModifySQLSortField(ByVal SQLStatement As String, ByVal SortName As String, Optional ByVal TagSortField As Boolean = True) As String
Dim astrSQLNew() As String, strAlias As String, strTagOrder As String '新SQLL语句数组; 别名; 排序标记符号
'字段名称; 字段全名(含表名或为AS前一个字符串); 输入名称(显示名称)

复制代码