【机房收费】-- 组合查询

【前言】
        组合这块被自己当做一个难点放到了最后，但是不管拖到时候还是自己解决，拖延并不能解决问题，去做才是。查询这块在学生信息系统里面大家都不陌生，所以要做这个组合查询当然要回头看看学生里面的查询原理。

（一）理清关系：

     这里面分为了三行三列。按行来说：每一行是一组，每一行组成了一个查询条件，这三个查询条件被两个组合关系所连接。And和Or，与和或。当第一行填满之后且选择组合关系才可以填第二行，第二行和第三行的关系也类似。如果不满足条件要给予相应的提示。按列来说：每一列下拉菜单的内容是相同的。
 

（二）特别注意：

       要查询某一天某一具体时间的内容时，一定要日期和时间同时选择，才可以实现。当字段名为姓名和性别时，只有“＝”　“＜＞”符号里不能出现其他的运算符。

 

（三）下面开始最重要的组合查询了，组合查询一定是源于简单查询，对于简单查询相信大家闭着眼睛也可以写的出来，机房系统里实在用的太多了，到处都是简单查询：select * from User_Info where cardno=01   同理，我们可以通过Where子句使用逻辑运算符将两个或者两个以上的条件表达式组合起来，构成综合检索条件，这样就是组合查询了。这里面的每一个条件都被填到了SQL语句对应的位置用And和OR连接起来构成多个条件的查询语句。
   

代码如下：

<span style="font-family:FangSong_GB2312;font-size:18px;"><strong>Private Sub cmdinquire_Click()

    MyFlexGrid.Clear
    MyFlexGrid.rows = 1
    txtSQL = "select * from Line_Info where "

    '只有一层查询
    If cboRelation1(0).Text = "" Then

        If cboFilename1(0).Text = "" Or cboSign1(0).Text = "" Or txtInquire1(0).Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        End If


        txtSQL = txtSQL & "" & Filename(cboFilename1(0).Text) & "" & cboSign1(0).Text & "'" & Trim(txtInquire1(0).Text) & "'"
        Set Mrc = ExecuteSQL(txtSQL, MsgText)
        If Mrc.EOF Then                                                         '数据库中无记录时
            MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
            txtInquire1(0).SetFocus
            txtInquire1(0).Text = ""
            MyFlexGrid.Clear
            Exit Sub

        Else                                                                    '数据库中有记录时

            '调用添加函数
            Call Result
        End If
    End If


    '有两层查询
    If cboRelation1(0).Text <> "" And cboRelation2.Text = "" Then
        If cboFilename1(0).Text = "" Or cboSign1(0).Text = "" Or txtInquire1(0).Text = "" Or _
            cboFilename2.Text = "" Or cboSign2.Text = "" Or txtInquire2.Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        Else
            If cboRelation1(0).Text = "与" Then
                txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "' " & " " & "and" & " " & _
                Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'"
                Set Mrc = ExecuteSQL(txtSQL, MsgText)
                If Mrc.EOF Then                                                 '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    txtInquire1(0).SetFocus
                    txtInquire1(0).Text = ""
                    txtInquire2.Text = ""
                    MyFlexGrid.Clear
                    Exit Sub
                Else                                                            '数据库中有记录时
                    Call Result                                                 '调用添加的自定义
                End If

            Else
                If cboRelation1(0).Text = "或" Then
                    txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "' " & " " & "or" & " " & _
                    Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'"
                    Set Mrc = ExecuteSQL(txtSQL, MsgText)
                    If Mrc.EOF Then                                             '数据库中无记录时
                        MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                        txtInquire1(0).SetFocus
                        txtInquire1(0).Text = ""
                        txtInquire2.Text = ""
                        MyFlexGrid.Clear
                        Exit Sub
                    Else                                                        '数据库中有记录时
                        Call Result                                             '调用添加的自定义
                    End If
                End If
            End If
        End If
    End If



    '有三层查询
    If cboRelation1(0).Text <> "" And cboRelation2.Text <> "" Then
        If cboFilename1(0).Text = "" Or cboSign1(0).Text = "" Or txtInquire1(0).Text = "" Or _
            cboFilename2.Text = "" Or cboSign2.Text = "" Or txtInquire2.Text = "" Or _
            cboFilename3.Text = "" Or cboSign3.Text = "" Or txtInquire3.Text = "" Then
            MsgBox "请把条件填写完整！", vbOKOnly + vbExclamation, "警告"
        Else                                                                    '分4种情况
            '与与关系
            If cboRelation1(0).Text = "与" And cboRelation2.Text = "与" Then
                txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "'" & " " & "And" & " " & _
                Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'" & " " & "And" & " " & _
                Filename(cboFilename3.Text) & cboSign3.Text & "'" & txtInquire3.Text & "' "
                Set Mrc = ExecuteSQL(txtSQL, MsgText)
                If Mrc.EOF Then                                                 '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    txtInquire1(0).SetFocus
                    txtInquire1(0).Text = ""
                    txtInquire2.Text = ""
                    txtInquire3.Text = ""
                    MyFlexGrid.Clear
                    Exit Sub
                Else                                                            '数据库中有记录时
                    Call Result                                                 '调用添加的自定义
                End If
            End If

            '与或关系
            If cboRelation1(0).Text = "与" And cboRelation2.Text = "或" Then
                txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "'" & " " & "And" & " " & _
                Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'" & " " & "Or" & " " & _
                Filename(cboFilename3.Text) & cboSign3.Text & "'" & txtInquire3.Text & "' "
                Set Mrc = ExecuteSQL(txtSQL, MsgText)
                If Mrc.EOF Then                                                 '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    txtInquire1(0).SetFocus
                    txtInquire1(0).Text = ""
                    txtInquire2.Text = ""
                    txtInquire3.Text = ""
                    MyFlexGrid.Clear
                    Exit Sub
                Else                                                            '数据库中有记录时
                    Call Result                                                 '调用添加的自定义
                End If
            End If

            '或或关系
            If cboRelation1(0).Text = "或" And cboRelation2.Text = "或" Then
                txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "'" & " " & "or" & " " & _
                Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'" & " " & "Or" & " " & _
                Filename(cboFilename3.Text) & cboSign3.Text & "'" & txtInquire3.Text & "' "
                Set Mrc = ExecuteSQL(txtSQL, MsgText)
                If Mrc.EOF Then                                                 '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    txtInquire1(0).SetFocus
                    txtInquire1(0).Text = ""
                    txtInquire2.Text = ""
                    txtInquire3.Text = ""
                    MyFlexGrid.Clear
                    Exit Sub
                Else                                                            '数据库中有记录时
                    Call Result                                                 '调用添加的自定义
                End If
            End If

            '或与关系
            If cboRelation1(0).Text = "或" And cboRelation2.Text = "与" Then
                txtSQL = txtSQL & Filename(cboFilename1(0).Text) & cboSign1(0).Text & "'" & txtInquire1(0).Text & "'" & " " & "or" & " " & _
                Filename(cboFilename2.Text) & cboSign2.Text & "'" & txtInquire2.Text & "'" & " " & "and" & " " & _
                Filename(cboFilename3.Text) & cboSign3.Text & "'" & txtInquire3.Text & "' "
                Set Mrc = ExecuteSQL(txtSQL, MsgText)
                If Mrc.EOF Then                                                 '数据库中无记录时
                    MsgBox "无此记录", vbOKOnly + vbExclamation, "警告！"
                    txtInquire1(0).SetFocus
                    txtInquire2.Text = ""
                    txtInquire2.Text = ""
                    txtInquire3.Text = ""
                    MyFlexGrid.Clear
                    Exit Sub
                Else                                                            '数据库中有记录时
                    Call Result                                                 '调用添加的自定义
                End If
            End If
        End If
    End If
End Sub

</strong></span>

【总结】

    大家一定不要有畏难心理，很多时候我们不是被问题打败，而是被自己的畏难心理打败，相信自己，去做去探索，总有收获的。

     感谢您的阅读~~