机房收费系统之组合查询

说到组合查询，其实我们并不陌生，在学生管理系统中，我们曾接触过比较简单的组合查询。

这次，在机房收费系统中，我们遇到了较为复杂一些。通过总结别人的博客，有一种相对简单一点儿的处理方式。这种方法只需考虑组合查询涉及到条件的数目，不需要考虑条件的具体内容，也就是说，如果我们要涉及的查询条件数不变，那么具体代码也就不会有太大的变化！

附一张组合查询条件图吧：

Private Sub cmdInquire_Click()

fgStuinfo.Clear   '每次查询前要清空列表，以免重复    Dim mrc As ADODB.Recordset    Dim txtSQL As String    Dim msgText As String        Dim dok1 As Boolean    Dim dok2 As Boolean    Dim dok3 As Boolean            Dim Title(0 To 6) As String  '如果条件内容改变，只需要将此处中具体内容改变即可

'注意这里的名称为SQL表中索要查询列的名称    Title(0) = "cardno"    Title(1) = "name"    Title(2) = "ondate"    Title(3) = "ontime"    Title(4) = "offdate"    Title(5) = "offtime"    Title(6) = "consumemoney"        Dim compose(0 To 1) As String    compose(0) = " and "    compose(1) = " or "        On Error Resume Next        txtSQL = "select * from loginrecord_info where "    '第一行条件满足    If Trim(comboFeild(0).Text) <> "" And combosign(0).Text <> "" And txtContent(0).Text <> "" Then        dok1 = True    Else        dok1 = False            End If        '第二行条件满足    If comboFeild(1).Text <> "" And combosign(1).Text <> "" And txtContent(1).Text <> "" Then        dok2 = True    Else        dok2 = False    End If        '第三行条件满足        If comboFeild(2).Text <> "" And combosign(2).Text <> "" And txtContent(2).Text <> "" Then        dok3 = True    Else        dok3 = False    End If        '一行条件也没有    If (dok1 = False And dok2 = False And dok3 = False) Then        If comboFeild(0).Text = "" Then            MsgBox "请输入字段名！"            Exit Sub        Else            If combosign(0).Text = "" Then                MsgBox "请输入操作符！"                Exit Sub            Else                If txtContent(0).Text = "" Then                    MsgBox "请输入要查询的内容！"                    Exit Sub                End If            End If        End If    End If            '有第一行条件    If dok1 = True Then        txtSQL = txtSQL & Title(comboFeild(0).ListIndex) & Trim(combosign(0).Text) & _            "'" & Trim(txtContent(0).Text) & "'"            End If        '有第二行条件    If dok2 = True Then        If dok1 = True Then            If ComboOperater(0).Text = "" Then                MsgBox "请选择组合关系！"                Exit Sub            Else                txtSQL = txtSQL & compose(ComboOperater(0).ListIndex) & Title(comboFeild(1).ListIndex) & Trim(combosign(1).Text) & _                    "'" & Trim(txtContent(1).Text) & "'"            End If        Else            txtSQL = txtSQL & Title(comboFeild(1).ListIndex) & Trim(combosign(1).Text) & _                "'" & Trim(txtContent(1).Text) & "'"                        End If    End If            '有第三行条件       If dok3 = True Then        If dok2 = True Then            If dok1 = True Then                If ComboOperater(1).Text = "" Then                    MsgBox "请选择第二个组合关系！"                    Exit Sub                Else                    txtSQL = txtSQL & compose(ComboOperater(1).ListIndex) & Title(comboFeild(2).ListIndex) & _                        combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                                        End If            Else   ’如果dok1=false                txtSQL = txtSQL & compose(ComboOperater(1).ListIndex) & Title(comboFeild(2).ListIndex) & _                    combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                            End If        Else            If dok1 = True Then                If ComboOperater(0).Text = "" Then                    MsgBox "请输入第一个组合关系！"                    Exit Sub                Else   'dok2=true,dok3=true,dok1=false                    txtSQL = txtSQL & compose(ComboOperater(0).ListIndex) & Title(comboFeild(2).Text) & _                        combosign(2).Text & "'" & Trim(txtContent(2).Text) & "'"                End If            Else              txtSQL = txtSQL & Title(comboFeild(2).ListIndex) & combosign(2).Text & _                "'" & Trim(txtContent(2).Text) & "'"            End If           End If    End If            Set mrc = ExecuteSQL(txtSQL, msgText)        With fgStuinfo        .Rows = 2        .CellAlignment = 4        .TextMatrix(1, 0) = "卡号"        .TextMatrix(1, 1) = "姓名"        .TextMatrix(1, 2) = "登录日期"        .TextMatrix(1, 3) = "登录时间"        .TextMatrix(1, 4) = "下机日期"        .TextMatrix(1, 5) = "下机时间"        .TextMatrix(1, 6) = "消费金额"                Do While Not mrc.EOF            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))            .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(5))            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(6))            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(7))            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(8))            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(10))            mrc.MoveNext                    Loop                If .TextMatrix(2, 0) = "" Then '如果一条结果也没有，进行提示！            MsgBox "没有符合您想要的结果"            Exit Sub        End If            End WithEnd Sub

在这里，我们还需注意listindex属性的含义！它的返回值为数字，对应上面我们事先声明的数组表达式！