如何编写Excel宏对比两个工作表中排除参数后的SQL查询

默认前提：两张待对比工作表分别命名为表1、表2，SQL查询统一存放在A列，第1行为表头，数据从第2行开始；SQL参数格式为@参数名（如果你的参数格式不同可后续修改匹配规则）。

实现逻辑 #

• 编写通用的参数清洗函数：通过正则匹配把SQL中所有符合格式的参数统一替换为固定占位符，消除参数值差异对对比的影响
• 逐行读取两张表的SQL内容，分别完成清洗后做文本对比
• 自动生成结果表，标记每行的对比状态，不一致的行同步展示两边清洗后的SQL内容方便排查

完整VBA代码 #

Sub 对比排除参数后的SQL()
    Dim ws1 As Worksheet, ws2 As Worksheet, resWs As Worksheet
    Dim lastRow As Long, i As Long
    Dim sql1 As String, sql2 As String, cleanSql1 As String, cleanSql2 As String
    
    ' 初始化工作表对象，可根据你的实际表名修改
    Set ws1 = ThisWorkbook.Worksheets("表1")
    Set ws2 = ThisWorkbook.Worksheets("表2")
    
    ' 创建结果工作表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("对比结果").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Set resWs = ThisWorkbook.Worksheets.Add
    resWs.Name = "对比结果"
    
    ' 写结果表表头
    resWs.Range("A1") = "行号"
    resWs.Range("B1") = "对比结果"
    resWs.Range("C1") = "表1清洗后SQL"
    resWs.Range("D1") = "表2清洗后SQL"
    
    ' 获取最大行数，取两个表的最大值
    lastRow = Application.WorksheetFunction.Max(ws1.Cells(Rows.Count, "A").End(xlUp).Row, ws2.Cells(Rows.Count, "A").End(xlUp).Row)
    
    ' 逐行对比
    For i = 2 To lastRow
        sql1 = Trim(ws1.Cells(i, "A").Value)
        sql2 = Trim(ws2.Cells(i, "A").Value)
        
        ' 清洗掉参数
        cleanSql1 = RemoveSqlParams(sql1)
        cleanSql2 = RemoveSqlParams(sql2)
        
        resWs.Cells(i, "A") = i
        If cleanSql1 = cleanSql2 Then
            resWs.Cells(i, "B") = "一致"
            resWs.Cells(i, "B").Interior.Color = RGB(0, 255, 0)
        Else
            resWs.Cells(i, "B") = "不一致"
            resWs.Cells(i, "B").Interior.Color = RGB(255, 0, 0)
            resWs.Cells(i, "C") = cleanSql1
            resWs.Cells(i, "D") = cleanSql2
        End If
    Next i
    
    ' 自动调整列宽
    resWs.Columns("A:D").AutoFit
    MsgBox "对比完成，结果已存到【对比结果】工作表"
End Sub

' SQL参数清洗函数，可修改正则规则匹配你的参数格式
Function RemoveSqlParams(sql As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
        .Global = True
        .IgnoreCase = True
        ' 这里是匹配@开头的参数，如果你的参数是其他格式比如${xxx}、%s等，修改Pattern即可
        .Pattern = "@\w+" ' 匹配@后面跟字母数字下划线的参数，如@user_id、@start_time
        ' 其他常用参数格式参考：
        ' .Pattern = "\$\{\w+\}" ' 匹配${xxx}格式参数
        ' .Pattern = "%[sdf]" ' 匹配%s、%d、%f格式占位符
    End With
    
    ' 把所有参数统一替换为?占位符
    RemoveSqlParams = regEx.Replace(sql, "?")
    
    ' 额外处理：去掉多余空格换行，避免格式差异导致误判
    RemoveSqlParams = Replace(Replace(Trim(RemoveSqlParams), vbCrLf, " "), vbTab, " ")
    Do While InStr(RemoveSqlParams, "  ") > 0
        RemoveSqlParams = Replace(RemoveSqlParams, "  ", " ")
    Loop
End Function

使用说明 #

• 运行宏前先修改代码中的工作表名、SQL所在列号，和你实际的表格结构对齐
• 根据你SQL中使用的参数格式，修改RemoveSqlParams函数里的正则匹配规则，常见格式已经在代码注释中给出参考
• 如果需要忽略大小写、忽略注释等额外对比逻辑，可在清洗函数中补充对应的处理步骤

内容的提问来源于stack exchange，提问作者Manasi Acharyya