3.MySQL查询交互的主代码

 

源代码

Sub ZhongTaiZhuChaXun01()
t = Timer

Sheets("中台接口查询").Range("d11:bz1000000").ClearContents

Dim ztshu1 As Long  '查询数据总条数变量(QH)
Dim iq1, h, iq2, iq3, i As Long 'FOR循环数量变量(QH)
Dim row As Long  '查询数据存表起始变量(QH)
Dim lie1 As Long  '查询结果总列数变量(QH)
Dim lie2 As Long  '查询总数总列数变量(QH)
Dim n As Long  '查询结果行数变量(QH)
Dim w As Long '进度条固定长度(QH)
Dim g As Single  '进度条变动长度(QH)
Dim tiao1 As Long  '批量查询条件最后行数变量(QH)
Dim zhusql1, sql As Variant  '主SQL变量(QH)
Dim jishusql1 As Variant   '计数SQL变量(QH)
Dim tiaojian1 As Variant   '条件SQL变量(QH)
Dim tiaojian2 As Variant   '批量条件SQL变量(QH)
Dim tiaojianzhi1 As Variant   '批量条件值变量(QH)
Dim Server01, Port01, Database01, UID01, PWD01 As String '定义登录信息常量(QH)
Dim cnn As New ADODB.Connection     '定义变量(QH)
Dim quehuirs2 As New ADODB.Recordset
Dim qh001 As Long

Set cnn = New ADODB.Connection
Set quehuirs2 = New ADODB.Recordset

With Sheets("login")  '获取数据库登录信息
    Server01 = .Range("c3").Value
    Port01 = .Range("c4").Value
    Database01 = .Range("c5").Value
    UID01 = .Range("c6").Value
    PWD01 = .Range("c7").Value
End With

zhusql1 = Sheets("配置表").Range("f50").Value  '主SQL获取(QH)
jishusql1 = Sheets("配置表").Range("e50").Value  '计数SQL获取(QH)

With Sheets("中台接口查询")
    qh001 = .Range("d7").End(xlToRight).Column
    If .Range("c5") = "单笔&模糊查询" Then
        For iq1 = 5 To qh001
           If .Cells(9, iq1) <> "" Then
               tiaojian1 = tiaojian1 & " and " & .Cells(8, iq1) & " like" & " '" & .Cells(9, iq1) & "' " '组合查询的条件语句部分(QH)
           End If
        Next
        
        cnn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server01 & ";Port=" & Port01 & ";Database=" & Database01 & ";UID=" & UID01 & ";PWD=" & PWD01 & ";OPTION=3;"
        cnn.Open
        sql = zhusql1 & " " & tiaojian1 '组合SQL脚本
'        .Range("a1") = sql  '调试点
        quehuirs2.Open sql, cnn
                
        lie1 = quehuirs2.Fields.Count + 4
        row = 10
        
        Do While Not quehuirs2.EOF()
            row = row + 1
            For h = 5 To lie1
                Cells(row, h) = quehuirs2.Fields(h - 5).Value
            Next
            quehuirs2.MoveNext
        Loop
        quehuirs2.Close: Set quehuirs2 = Nothing
        cnn.Close: Set cnn = Nothing
            
    Else
    
        For iq3 = 5 To qh001    '获取批量查询条件语句(QH)
            If .Range("c10") = .Cells(7, iq3) Then
                tiaojian2 = " and " & .Cells(8, iq3) & " in "
            End If
        Next
        
            tiao1 = .Range("c65536").End(xlUp).row       '获取查询数据最大行(QH)
            If tiao1 - 10 = 1 Then    '判断是否只有一条记录(QH)
                tiaojianzhi1 = "('" & .Cells(tiao1, 3) & "')"        '是一条记录则取此格式(QH)
            Else        '否则(QH)
                If tiao1 - 10 = 2 Then     '判断是否只有两条记录(QH)
                    tiaojianzhi1 = "('" & .Cells(tiao1 - 1, 3) & "'," & vbLf & "'" & .Cells(tiao1, 3) & "')"      '是两条记录则取此格式(QH)
                Else    '否则(QH)
                    If tiao1 - 10 > 2 Then     '判断是否大于三条记录(QH)
                        For i = 11 To tiao1
                            If i = 11 Then
                                tiaojianzhi1 = "('" & .Cells(i, 3) & "'," & vbLf
                            Else
                                If i <> tiao1 Then
                                    tiaojianzhi1 = tiaojianzhi1 & "'" & .Cells(i, 3) & "'," & vbLf
                                Else
                                    tiaojianzhi1 = tiaojianzhi1 & "'" & .Cells(tiao1, 3) & "')"
                                End If
                            End If
                        Next i
                   End If
                End If
            End If
            
        cnn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server01 & ";Port=" & Port01 & ";Database=" & Database01 & ";UID=" & UID01 & ";PWD=" & PWD01 & ";OPTION=3;"
        cnn.Open
        sql = zhusql1 & " " & tiaojian2 & " " & tiaojianzhi1 '组合SQL脚本
'        .Range("a1") = sql  '调试点
        quehuirs2.Open sql, cnn
        
        lie1 = quehuirs2.Fields.Count + 4
        row = 10
        
        Do While Not quehuirs2.EOF()
            row = row + 1
            For h = 5 To lie1
                Cells(row, h) = quehuirs2.Fields(h - 5).Value
            Next
            quehuirs2.MoveNext
        Loop
        
        quehuirs2.Close: Set quehuirs2 = Nothing
        cnn.Close: Set cnn = Nothing
        
    End If




End With
End Sub