基础很重要，查询如何“组合”！谈谈组合查询

    前言：

    在之前是没有接触过组合查询的。一听说，哇！组合查询！什么？没有听过，第一个反应。不会，哇塞，肯定好难。听别人的一说，哦！更加明白了，我做不出来！呵呵，这样一个心态。我开始了“组合”查询。后来当然是完成了，但我发现事情远远没有做完那么简单！

首先，还是先说说这些组合查询吧！

如下图，红色框的内容利用蓝色框的关系与下面黄色框的内容结合。进而来返回表中的数据

利用数组做为每个字段名的变量，这个问题就迎刃而解了。请看如下代码

第一部分：使文本框合法输入，并利用变量将字段内容取出来

    Dim mrc As ADODB.Recordset    Dim strSQL As String    Dim strMsgtext As String    Dim Field(2) As String    Dim Operator(2) As String    Dim Logic(1) As String    MyFlexGrid.Clear    '确保输入条件合法    If Combo1.Text = "" Then        MsgBox "请输入字段名！", vbOKOnly + vbExclamation, "温馨提示！"        Combo1.SetFocus        Exit Sub    Else        Select Case Combo1.ListIndex        Case 0            Field(0) = "cardno"        Case 1            Field(0) = "name"        Case 2            Field(0) = "onlinetime"        Case 3            Field(0) = "onlinedate"        End Select    End If    If Combo4.Text = "" Then        MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "温馨提示！"        Combo4.SetFocus        Exit Sub    Else        Select Case Combo4.ListIndex        Case 0            Operator(0) = "<"        Case 1            Operator(0) = ">"        Case 2            Operator(0) = "="        Case 3            Operator(0) = "<>"        End Select        If Txtworda.Text = "" Then            MsgBox "请输入要查询的内容1!", vbOKOnly + vbExclamation, "温馨提示！"            Txtworda.SetFocus            Exit Sub        End If    End If    If Combo7.Text <> "" = True Then        '指定第一个逻辑字符        Select Case Combo7.ListIndex        Case 0            Logic(0) = "and"        Case 1            Logic(0) = "or"        End Select        If Combo2.Text = "" Then            MsgBox "请输入字段名！", vbOKOnly + vbExclamation, "温馨提示！"            Combo2.SetFocus            Exit Sub        Else            '指定第二个字段名字符            Select Case Combo2.ListIndex            Case 0                Field(1) = "cardno"            Case 1                Field(1) = "name"            Case 2                Field(1) = "onlinetime"            Case 3                Field(1) = "onlinedate"            End Select            If Combo5.Text = "" Then                MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "温馨提示！"                Combo5.SetFocus                Exit Sub            Else                '指定第二个关系运算符字符                Select Case Combo5.ListIndex                Case 0                    Operator(1) = "<"                Case 1                    Operator(1) = ">"                Case 2                    Operator(1) = "="                Case 3                    Operator(1) = "<>"                End Select                If Txtwordb.Text = "" Then                    MsgBox "请输入要查询的内容！", vbOKOnly + vbExclamation, "温馨提示！"                    Txtwordb.SetFocus                    Exit Sub                End If            End If        End If    End If    If Combo8.Text <> "" = True Then        '指定第二个逻辑符字符串        Select Case Combo8.ListIndex        Case 0            Logic(1) = "and"        Case 1            Logic(1) = "or"        End Select        If Combo3.Text = "" Then            MsgBox "字段名！", vbOKOnly + vbExclamation, "温馨提示！"            Combo3.SetFocus            Exit Sub        Else            '指定第三个字段字符串            Select Case Combo3.ListIndex            Case 0                Field(2) = "cardno"            Case 1                Field(2) = "name"            Case 2                Field(2) = "onlinetime"            Case 3                Field(2) = "onlinedate"            End Select            If Combo6.Text = "" Then                MsgBox "请输入操作符！", vbOKOnly + vbExclamation, "温馨提示！"                Combo6.SetFocus                Exit Sub            Else                '指定第三个关系运算符字符串                Select Case Combo6.ListIndex                Case 0                    Operator(2) = "<"                Case 1                    Operator(2) = ">"                Case 2                    Operator(2) = "="                Case 3                    Operator(2) = "<>"                End Select                If Txtwordc.Text = "" Then                    MsgBox "请输入要查询的内容！", vbOKOnly + vbExclamation, "温馨提示！"                    Txtwordc.SetFocus                    Exit Sub                End If            End If        End If    End If

这段代码的核心应该就一点

1.数组和select语句运用，来返回文本框查询内容

第二部分：判断逻辑符之间关系，执行sql 语句

'当第一个逻辑关系为空时，执行下面语句    If Combo7.Text = "" Then        strSQL = "select * from offlineinfo where" & " " & Field(0) & "" & Operator(0) & " '" & Txtworda.Text & "'"        Set mrc = ExecuteSQL(strSQL, strMsgtext)        With MyFlexGrid            Do While Not mrc.EOF                .TextMatrix(0, 0) = "卡号"                .TextMatrix(0, 1) = "姓名"                .TextMatrix(0, 2) = "上机日期"                .TextMatrix(0, 3) = "上机时间"                .ColWidth(1) = 1000                .ColWidth(0) = 1000                .ColWidth(2) = 1000                .ColWidth(3) = 1000                .Rows = .Rows + 1                .CellAlignment = 4                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))                mrc.MoveNext            Loop        End With    Else        If Combo8.Text = "" Then            strSQL = "select * from offlineinfo where" & " " & Field(0) & " " & Operator(0) & " '" & Txtworda.Text & "' " & Logic(0) & " " & Field(1) & " " & Operator(1) & "'" & Txtwordb.Text & "'"            Set mrc = ExecuteSQL(strSQL, strMsgtext)            With MyFlexGrid                Do While Not mrc.EOF                    .TextMatrix(0, 0) = "卡号"                    .TextMatrix(0, 1) = "姓名"                    .TextMatrix(0, 2) = "上机日期"                    .TextMatrix(0, 3) = "上机时间"                    .ColWidth(1) = 1000                    .ColWidth(0) = 1000                    .ColWidth(2) = 1000                    .ColWidth(3) = 1000                    .Rows = .Rows + 1                    .CellAlignment = 4                    .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))                    .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))                    .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))                    .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))                    mrc.MoveNext                Loop            End With        End If        If Combo8.Text <> "" Then            strSQL = "select * from offlineinfo where" & " " & Field(0) & " " & Operator(0) & " " & Txtworda.Text & " " & Logic(0) & " " & Field(1) & " " & Operator(1) & " '" & Txtwordb.Text & "' " & Logic(1) & " " & Field(2) & " " & Operator(2) & " '" & Txtwordc.Text & "'"            Set mrc = ExecuteSQL(strSQL, strMsgtext)            If mrc.BOF And mrc.EOF Then                MsgBox "没有该条记录！", vbOKOnly + vbExclamation, "温馨提示！"                End            End If            With MyFlexGrid                Do While Not mrc.EOF                    .TextMatrix(0, 0) = "卡号"                    .TextMatrix(0, 1) = "姓名"                    .TextMatrix(0, 2) = "上机日期"                    .TextMatrix(0, 3) = "上机时间"                    .ColWidth(1) = 1000                    .ColWidth(0) = 1000                    .ColWidth(2) = 1000                    .ColWidth(3) = 1000                    .Rows = .Rows + 1                    .CellAlignment = 4                    .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))                    .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))                    .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))                    .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))                    mrc.MoveNext                Loop            End With        End If    End IfEnd Sub

这段代码的核心应该是这三句sql语句了。

    总结：

    组合查询就是这样。没有我传闻那样骇闻，也没有我想象中如此不能为之。仔细一想，首先还是自己把自己困住了。给了自己一个不会的理由，然后我很“淡定”的磨了很多天。其实这里面，我认为最为精到还是那几个数组的应用。看来还是基础工作没有做透啊！以前的债啊，还好现在“还回来了”！keep moving