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

前端之家收集整理的这篇文章主要介绍了【机房收费】-- 组合查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
【前言】
组合这块被自己当做一个难点放到了最后,但是不管拖到时候还是自己解决,拖延并不能解决问题,去做才是。查询这块在学生信息系统里面大家都不陌生,所以要做这个组合查询当然要回头看看学生里面的查询原理。


(一)理清关系:

这里面分为了三行三列。按行来说:每一行是一组,每一行组成了一个查询条件,这三个查询条件被两个组合关系所连接。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 "无此记录","警告!"
            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 "请把条件填写完整!","警告"
        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 "无此记录","警告!"
                    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 "无此记录","警告!"
                        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 "请把条件填写完整!","警告"
        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,"警告!"
                    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,"警告!"
                    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,"警告!"
                    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,"警告!"
                    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>

【总结】

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

感谢您的阅读~~

猜你在找的VB相关文章