前提:
写代码有的时候会执行的次数很多,所以需要优化,让运行变得更快。
内容:
在串一遍组合查询的思路
任务一:进行关联性判断,(就是如果第一个“与”“或”之不存在,就只执行第一行,,如果第二个“与”“或”不存在,就执行两行,否则执行第三行)
任务二:进行判断,信息不完整不能有空值,(例如:在只执行第一行的前提下,就绝不执行第一行有空值)
任务三:查询(将所有的查询写出)
任务四:显示(代码如下)
'任务四: 显示信息
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>
总结:
机房的组合查询还没有进一步的优化,目前基本功能实现了,优化下文待续。