机房收费系统——组合查询 1.0

一、引言

组合查询这个问题是我机房收费系统遇到的第一个难题！而且困扰了我很久！
主要有两个问题：
第一是逻辑上的问题！
第二是数据库查询上的问题！
接下来就说说这两个问题。

二、组合逻辑

一共有三组查询条件，两个组合！
难点：用户选择选择条件及其组合情况的判断
最简单的解决办法就算是按照正常的逻辑，一条一条的进行判断！
简单的画了一个流程图！

这个流程非常容易理解！代码也很容易实现！

三、组合查询语句

1、只有一个条件的时候的查询

txtSQL = "select * from student_info where " & field(Trim(ziduancombo1.Text)) & Trim(caozuofucombo1.Text) & "'" & Trim(txtchaxun1.Text) & "'"

注：field为用来转换combo中的选项，将他们与数据库中的列名对应起来！
field用之前需要定义！

Public Function field(i As String) As StringSelect Case ziduancombo1.Text    Case "卡号"field = "cardno"    Case "学号"field = "studentno"    Case "姓名"field = "studentname"    Case "性别"field = "sex"    Case "班级"field = "class"    Case "年级"field = "grade"    Case "系别"field = "department"    Case "日期"field = "date"    Case "时间"field = "time"                        End Function

2、两个条件进行组合查询语句

txtSQL = txtSQL & strzuhecombo1 & " " & field(Trim(ziduancombo2.Text)) & Trim(caozuofucombo2.Text) & "'" & Trim(txtchaxun2.Text) & "'"

注：strzuhecombo1需要定义，用来转换“或”和“与”，使之能够被数据库识别

  Select Case zuhecombo1.Text      Case "与"         strzuhecombo1 = "and"      Case "或"             strzuhecombo1 = "or"  End Select

3、三个条件进行组合查询语句

txtSQL = txtSQL & strzuhecombo2 & " " & field(Trim(ziduancombo3.Text)) & Trim(caozuofucombo3.Text) & "'" & Trim(txtchaxun3.Text) & "'"

四、整个过程代码

Private Sub cmdok_Click()If Trim(ziduancombo1.Text) = "" Then    MsgBox "请选择字段！", vbOKOnly + vbExclamation, "警告"    ziduancombo1.SetFocusElse    If Trim(caozuofucombo1.Text) = "" Then        MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"        caozuofucombo1.SetFocus    Else        If Trim(txtchaxun1.Text) = "" Then            MsgBox "请输入要查询的内容！", vbOKOnly + vbExclamation, "警告"            txtchaxun1.SetFocus            Exit Sub        Else            '执行查询             txtSQL = "select * from student_info where " & field(Trim(ziduancombo1.Text)) & Trim(caozuofucombo1.Text) & "'" & Trim(txtchaxun1.Text) & "'"            '判断第一个组合条件时为空            If Trim(zuhecombo1.Text) = "" Then '此时没有组合查询            Set mrc = executeSQL(txtSQL, msgtext)                If mrc.EOF = True Then                    MsgBox "没有该记录！", vbOKOnly + vbExclamation, "提示"                    Exit Sub                End If            Else                '选择了组合条件1时进行组合查询                    If Trim(ziduancombo2.Text) = "" Then                        MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"                        ziduancombo2.SetFocus                    Else                        If Trim(caozuofucombo2.Text) = "" Then                            MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"                            caozuofucombo2.SetFocus                        Else                            If Trim(txtchaxun2.Text) = "" Then                                MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"                                txtchaxun2.SetFocus                                Exit Sub                            Else                                If Trim(zuhecombo2.Text) = "" Then '第二个组合条件为空，进行前两个的组合查询                                '执行查询                                Dim strzuhecombo1, strzuhecombo2 '定义变量用来转换与和或                                If zuhecombo1.Text <> "" Then                                 Select Case zuhecombo1.Text                                  Case "与"                                   strzuhecombo1 = "and"                                  Case "或"                                   strzuhecombo1 = "or"                                End Select                                End If                                 txtSQL = txtSQL & strzuhecombo1 & " " & field(Trim(ziduancombo2.Text)) & Trim(caozuofucombo2.Text) & "'" & Trim(txtchaxun2.Text) & "'"                                Set mrc = executeSQL(txtSQL, msgtext)                                    If mrc.EOF = True Then                                        MsgBox "没有此条记录！", vbOKOnly + vbExclamation, "提示"                                        ziduancombo1.Text = ""                                        ziduancombo2.Text = ""                                        caozuofucombo1.Text = ""                                        caozuofucombo2.Text = ""                                        txtchaxun1.Text = ""                                        txtchaxun2.Text = ""                                        zuhecombo1.Text = ""                                        Exit Sub                                     End If                                Else            '如果第二个组合条件不为空时，对第三行的条件进行检查                                    If Trim(ziduancombo3.Text) = "" Then                                        MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"                                        ziduancombo3.SetFocus                                    Else                                        If Trim(caozuofucombo3.Text) = "" Then                                            MsgBox "请将查询条件填写完整", vbOKOnly + vbExclamation, "提示"                                            caozuofucombo3.SetFocus                                        Else                                            If Trim(txtchaxun3.Text) = "" Then                                                MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"                                                txtchaxun3.SetFocus                                                Exit Sub                                            Else    '都满足条件后，进行组合查询！                                            Select Case zuhecombo2.Text                                                Case "与"                                                    strzuhecombo2 = "and"                                                Case "或"                                                    strzuhecombo2 = "or"                                            End Select                                                txtSQL = txtSQL & strzuhecombo2 & " " & field(Trim(ziduancombo3.Text)) & Trim(caozuofucombo3.Text) & "'" & Trim(txtchaxun3.Text) & "'"                                                Set mrc = executeSQL(txtSQL, msgtext)                                                If mrc.EOF = True Then                                                    MsgBox "没有此条查询记录！", vbOKOnly + vbExclamation, "提示"                                                    ziduancombo1.Text = ""                                                    ziduancombo2.Text = ""                                                    ziduancombo3.Text = ""                                                    caozuofucombo1.Text = ""                                                    caozuofucombo2.Text = ""                                                    caozuofucombo3.Text = ""                                                    txtchaxun1.Text = ""                                                    txtchaxun2.Text = ""                                                    txtchaxun3.Text = ""                                                    zuhecombo1.Text = ""                                                    zuhecombo2.Text = ""                                                End If                                            End If                                        End If                                    End If                                End If                            End If                        End If                    End If            End If        End If    End IfEnd If'显示查询结果            With MSFgxinxichaxun         .Rows = 1         .CellAlignment = 4         .TextMatrix(0, 0) = "学号"         .TextMatrix(0, 1) = "姓名"         .TextMatrix(0, 2) = "卡号"         .TextMatrix(0, 3) = "金额"         .TextMatrix(0, 4) = "系别"         .TextMatrix(0, 5) = "年级"         .TextMatrix(0, 6) = "班级"         .TextMatrix(0, 7) = "性别"         .TextMatrix(0, 8) = "状态"         .TextMatrix(0, 9) = "备注"         .TextMatrix(0, 10) = "类型"         .TextMatrix(0, 11) = "日期"         .TextMatrix(0, 12) = "时间"        Do While Not mrc.EOF             .Rows = .Rows + 1             .CellAlignment = 4             .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))             .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))             .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(0))             .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))             .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))             .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))             .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))             .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))             .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))             .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))             .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))             .TextMatrix(.Rows - 1, 11) = Trim(mrc.Fields(12))             .TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(13))             '移动到下一条记录             mrc.MoveNext         Loop         End WithEnd Sub

一篇文章写下来，觉得组合查询并没有那么难！凡事都需要一个过程！学过了就要学会沉淀，这样才能走的更远！