基础很重要,查询如何“组合”!谈谈组合查询
前言:
在之前是没有接触过组合查询的。一听说,哇!组合查询!什么?没有听过,第一个反应。不会,哇塞,肯定好难。听别人的一说,哦!更加明白了,我做不出来!呵呵,这样一个心态。我开始了“组合”查询。后来当然是完成了,但我发现事情远远没有做完那么简单!
首先,还是先说说这些组合查询吧!
如下图,红色框的内容利用蓝色框的关系与下面黄色框的内容结合。进而来返回表中的数据
利用数组做为每个字段名的变量,这个问题就迎刃而解了。请看如下代码
第一部分:使文本框合法输入,并利用变量将字段内容取出来
Dim mrc As ADODB.Recordset
Dim strSQL As String
Dim strMsgtext As String
Dim Field(2) As String
Dim Operator(2) As String
Dim Logic(1) As String
MyFlexGrid.Clear
'确保输入条件合法
If Combo1.Text = "" Then
MsgBox "请输入字段名!", vbOKOnly + vbExclamation, "温馨提示!"
Combo1.SetFocus
Exit Sub
Else
Select Case Combo1.ListIndex
Case 0
Field(0) = "cardno"
Case 1
Field(0) = "name"
Case 2
Field(0) = "onlinetime"
Case 3
Field(0) = "onlinedate"
End Select
End If
If Combo4.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "温馨提示!"
Combo4.SetFocus
Exit Sub
Else
Select Case Combo4.ListIndex
Case 0
Operator(0) = "<"
Case 1
Operator(0) = ">"
Case 2
Operator(0) = "="
Case 3
Operator(0) = "<>"
End Select
If Txtworda.Text = "" Then
MsgBox "请输入要查询的内容1!", vbOKOnly + vbExclamation, "温馨提示!"
Txtworda.SetFocus
Exit Sub
End If
End If
If Combo7.Text <> "" = True Then
'指定第一个逻辑字符
Select Case Combo7.ListIndex
Case 0
Logic(0) = "and"
Case 1
Logic(0) = "or"
End Select
If Combo2.Text = "" Then
MsgBox "请输入字段名!", vbOKOnly + vbExclamation, "温馨提示!"
Combo2.SetFocus
Exit Sub
Else
'指定第二个字段名字符
Select Case Combo2.ListIndex
Case 0
Field(1) = "cardno"
Case 1
Field(1) = "name"
Case 2
Field(1) = "onlinetime"
Case 3
Field(1) = "onlinedate"
End Select
If Combo5.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "温馨提示!"
Combo5.SetFocus
Exit Sub
Else
'指定第二个关系运算符字符
Select Case Combo5.ListIndex
Case 0
Operator(1) = "<"
Case 1
Operator(1) = ">"
Case 2
Operator(1) = "="
Case 3
Operator(1) = "<>"
End Select
If Txtwordb.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "温馨提示!"
Txtwordb.SetFocus
Exit Sub
End If
End If
End If
End If
If Combo8.Text <> "" = True Then
'指定第二个逻辑符字符串
Select Case Combo8.ListIndex
Case 0
Logic(1) = "and"
Case 1
Logic(1) = "or"
End Select
If Combo3.Text = "" Then
MsgBox "字段名!", vbOKOnly + vbExclamation, "温馨提示!"
Combo3.SetFocus
Exit Sub
Else
'指定第三个字段字符串
Select Case Combo3.ListIndex
Case 0
Field(2) = "cardno"
Case 1
Field(2) = "name"
Case 2
Field(2) = "onlinetime"
Case 3
Field(2) = "onlinedate"
End Select
If Combo6.Text = "" Then
MsgBox "请输入操作符!", vbOKOnly + vbExclamation, "温馨提示!"
Combo6.SetFocus
Exit Sub
Else
'指定第三个关系运算符字符串
Select Case Combo6.ListIndex
Case 0
Operator(2) = "<"
Case 1
Operator(2) = ">"
Case 2
Operator(2) = "="
Case 3
Operator(2) = "<>"
End Select
If Txtwordc.Text = "" Then
MsgBox "请输入要查询的内容!", vbOKOnly + vbExclamation, "温馨提示!"
Txtwordc.SetFocus
Exit Sub
End If
End If
End If
End If
这段代码的核心应该就一点
1.数组和select语句运用,来返回文本框查询内容
第二部分:判断逻辑符之间关系,执行sql 语句
'当第一个逻辑关系为空时,执行下面语句
If Combo7.Text = "" Then
strSQL = "select * from offlineinfo where" & " " & Field(0) & "" & Operator(0) & " '" & Txtworda.Text & "'"
Set mrc = ExecuteSQL(strSQL, strMsgtext)
With MyFlexGrid
Do While Not mrc.EOF
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.ColWidth(1) = 1000
.ColWidth(0) = 1000
.ColWidth(2) = 1000
.ColWidth(3) = 1000
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
mrc.MoveNext
Loop
End With
Else
If Combo8.Text = "" Then
strSQL = "select * from offlineinfo where" & " " & Field(0) & " " & Operator(0) & " '" & Txtworda.Text & "' " & Logic(0) & " " & Field(1) & " " & Operator(1) & "'" & Txtwordb.Text & "'"
Set mrc = ExecuteSQL(strSQL, strMsgtext)
With MyFlexGrid
Do While Not mrc.EOF
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.ColWidth(1) = 1000
.ColWidth(0) = 1000
.ColWidth(2) = 1000
.ColWidth(3) = 1000
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
mrc.MoveNext
Loop
End With
End If
If Combo8.Text <> "" Then
strSQL = "select * from offlineinfo where" & " " & Field(0) & " " & Operator(0) & " " & Txtworda.Text & " " & Logic(0) & " " & Field(1) & " " & Operator(1) & " '" & Txtwordb.Text & "' " & Logic(1) & " " & Field(2) & " " & Operator(2) & " '" & Txtwordc.Text & "'"
Set mrc = ExecuteSQL(strSQL, strMsgtext)
If mrc.BOF And mrc.EOF Then
MsgBox "没有该条记录!", vbOKOnly + vbExclamation, "温馨提示!"
End
End If
With MyFlexGrid
Do While Not mrc.EOF
.TextMatrix(0, 0) = "卡号"
.TextMatrix(0, 1) = "姓名"
.TextMatrix(0, 2) = "上机日期"
.TextMatrix(0, 3) = "上机时间"
.ColWidth(1) = 1000
.ColWidth(0) = 1000
.ColWidth(2) = 1000
.ColWidth(3) = 1000
.Rows = .Rows + 1
.CellAlignment = 4
.TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
.TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(4))
.TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))
.TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
mrc.MoveNext
Loop
End With
End If
End If
End Sub
这段代码的核心应该是这三句sql语句了。
总结:
组合查询就是这样。没有我传闻那样骇闻,也没有我想象中如此不能为之。仔细一想,首先还是自己把自己困住了。给了自己一个不会的理由,然后我很“淡定”的磨了很多天。其实这里面,我认为最为精到还是那几个数组的应用。看来还是基础工作没有做透啊!以前的债啊,还好现在“还回来了”!keep moving