（六）组合查询—机房收费系统

最新推荐文章于 2019-08-25 19:53:09 发布

原创最新推荐文章于 2019-08-25 19:53:09 发布 · 722 阅读

0 ·

CC 4.0 BY-SA版权

机房收费系统专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种针对数据库查询的优化方法，通过使用Select Case语句来转换字段名称，并根据不同的条件组合形成SQL查询语句，提高了查询效率。

前提：

写代码有的时候会执行的次数很多，所以需要优化，让运行变得更快。

内容：

在串一遍组合查询的思路

任务一：进行关联性判断，（就是如果第一个“与”“或”之不存在，就只执行第一行，，如果第二个“与”“或”不存在，就执行两行，否则执行第三行）

任务二：进行判断，信息不完整不能有空值，（例如：在只执行第一行的前提下，就绝不执行第一行有空值）

任务三：查询（将所有的查询写出）

任务四：显示（代码如下）

'任务四: 显示信息


   With myFlexGrid1
                .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) = "备注"
                
                
    '            任务四:显示查询数据
    
                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(6))
                    .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
                    If IsNull(mrc.Fields(8)) Then
                    .TextMatrix(.Rows - 1, 4) = ""
                    Else
                    .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8))
                    End If
                    If IsNull(mrc.Fields(9)) Then
                    .TextMatrix(.Rows - 1, 5) = ""
                    Else
                    .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9))
                    End If
                    .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(11))
                    .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(12))
                    .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(13))
                 
                    mrc.MoveNext
    '                    移动到下一条数据
                Loop
            End With
            
        mrc.Close

之前的时候写的很多人后一点的减少，成了这个样子。

数据库查询的优化代码

<span style="font-size:24px;"><span style="font-size:24px;">Public str,  str1,str2 As String</span></span>

<span style="font-size:24px;"><span style="font-size:24px;"> </span><pre name="code" class="vb"><span style="font-size:24px;">Select Case str
            Case "卡号"
                str = "cardno"
            Case "姓名"
                str = "studentname"
            Case "上机日期"
                str = "ondate"
            Case "上机时间"
                str = "ontime"
            Case "下机日期"
                str = "offdate"
            Case "下机时间"
                str = "offtime"
            Case "消费金额"
                str = "consume"
            Case "余额"
                str = "cash"
            Case "备注"
                str = "status"
            Case Else
                str = ""
End select
</span></span>

<span style="font-size:24px;"><span style="font-size:24px;">Select Case str1
            Case "卡号"
                str1 = "cardno"
            Case "姓名"
                str1 = "studentname"
            Case "上机日期"
                str1 = "ondate"
            Case "上机时间"
                str1 = "ontime"
            Case "下机日期"
                str1 = "offdate"
            Case "下机时间"
                str1 = "offtime"
            Case "消费金额"
                str1 = "consume"
            Case "余额"
                str1 = "cash"
            Case "备注"
                str1 = "status"
            Case Else
                str1= ""</span></span>

<span style="font-size:24px;"><span style="font-size:24px;">End select</span></span>

str2的select case 同理

<span style="font-size:24px;">If Combo1.Text = "" Or Combo4.Text = "" Or Text1.Text = "" Then MsgBox "第一行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub</span>

<span style="font-size:24px;">If Combo2.Text = "" Or Combo5.Text = "" Or Text2.Text = "" Then MsgBox "第二行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub</span>

<span style="font-size:24px;">If Combo3.Text = "" Or Combo6.Text = "" Or Text3.Text = "" Then MsgBox "第三行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub</span>

<span style="font-size:24px;"> txtSQL = "select * from line_Info where " & str & Combo4.Text & "'" & Trim(Text1.Text) & "' " & one & str1 & Combo5.Text & "'" & Trim(Text2.Text) & "'" & two & str2 & Combo6.Text & "'" & Trim(Text3.Text) & "'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
        If mrc.EOF Then
            MsgBox "没有信息", vbOKOnly + vbExclamation, "提示"</span>

进一步优化的代码

<span style="font-size:24px;">s = 0
If one = "" Then
    
    If Combo1.Text = "" Or Combo4.Text = "" Or Text1.Text = "" Then MsgBox "第一行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub
    txtSQL = "select * from line_Info where " & Co(Combo1.Text) & z & Combo4.Text & "'" & Trim(Text1.Text) & "' "
            Set mrc = ExecuteSQL(txtSQL, MsgText)
ElseIf two = "" Then
     
     If Combo2.Text = "" Or Combo5.Text = "" Or Text2.Text = "" Then MsgBox "第二行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub
     txtSQL = "select * from line_Info where " & Co(Combo1.Text) & z & Combo4.Text & "'" & Trim(Text1.Text) & "' " & one & Co(Combo2.Text) & x & Combo5.Text & "'" & Trim(Text2.Text) & "'"
      Set mrc = ExecuteSQL(txtSQL, MsgText)
Else
     
     If Combo3.Text = "" Or Combo6.Text = "" Or Text3.Text = "" Then MsgBox "第三行信息不完整，请重新输入。", vbOKOnly + vbExclamation, "提示": Exit Sub
     txtSQL = "select * from line_Info where " & Co(Combo1.Text) & z & Combo4.Text & "' " & Trim(Text1.Text) & "' " & one & Co(Combo2.Text) & x & Combo5.Text & "'" & Trim(Text2.Text) & "' " & two & Co(Combo3.Text) & y & Combo6.Text & "'" & Trim(Text3.Text) & "'"
     Set mrc = ExecuteSQL(txtSQL, MsgText)
End If
        If mrc.EOF Then
            MsgBox "没有信息", vbOKOnly + vbExclamation, "提示"
        End If</span>

select case

用在了模块里

<span style="font-size:24px;">Public Function Co(str)
        s = s + 1 '第几次执行
        Select Case str
            Case "卡号"
                str = "cardno"
            Case "姓名"
                str = "studentname"
            Case "上机日期"
                str = "ondate"
            Case "上机时间"
                str = "ontime"
            Case "下机日期"
                str = "offdate"
            Case "下机时间"
                str = "offtime"
            Case "消费金额"
                str = "consume"
            Case "余额"
                str = "cash"
            Case "备注"
                str = "status"
            Case Else
                str = ""
                        
        End Select
If s = 1 Then
z = str
ElseIf s = 2 Then
x = str
Else
y = str
End If


End Function</span>

总结：

机房的组合查询还没有进一步的优化，目前基本功能实现了，优化下文待续。