机房收费系统之组合查询

        开始在整组合查询的时候没有看大家的博客，还想着不看大家的博客，要自己解决，就按照自己的思路写呀写，那么多的代码，虽然最后功能实现了，但是那些代码着实看着不舒服。

    先说一下自己的思路，先判断只有一个组合关系的情况，如果有组合关系还要判断是与的情况还是或的情况。特别是如果有两个组合关系的时候还要分四种情况：第一个组合关系是与，第二个是或；两个都为与；两个都为或；第一个是或，第二个是与。“或”连接的时候用"or"，“与”连接的时候用“and”。开始以为连接数据库时是不识别“与”字和“或”字的，看大家的博客才知道直接将组合关系与字段名、操作名、要查询的内容一样直接用连接符连接就可以了。

    看大家的博客，组合查询只有很少的代码就实现了，大致思路是先判断没有组合关系时，执行SQL语句，否则有一个组合关系时执行SQL语句，最后就是有两个组合关系都不为空时，执行SQL语句，在最后的时候将满足的条件显示在MSHFlexGrid控件的表格中。可能这样说的不是很明白，以学生上机统计信息查询为例，代码如下：

  （1） formLoad加载项：

 Private Sub Form_Load()
      Me.Height = 8985
      Me.Width = 13575
      Combo1.AddItem "卡号"
      Combo1.AddItem "姓名"
      Combo1.AddItem "上机日期"
      Combo1.AddItem "上机时间"
      Combo1.AddItem "下机日期"
      Combo1.AddItem "下机时间"
      Combo1.AddItem "消费金额"
      Combo1.AddItem "余额"
      Combo1.AddItem "备注"
      
      Combo2.AddItem "卡号"
      Combo2.AddItem "姓名"
      Combo2.AddItem "上机日期"
      Combo2.AddItem "上机时间"
      Combo2.AddItem "下机日期"
      Combo2.AddItem "下机时间"
      Combo2.AddItem "消费金额"
      Combo2.AddItem "余额"
      Combo2.AddItem "备注"
      
      Combo3.AddItem "卡号"
      Combo3.AddItem "姓名"
      Combo3.AddItem "上机日期"
      Combo3.AddItem "上机时间"
      Combo3.AddItem "下机日期"
      Combo3.AddItem "下机时间"
      Combo3.AddItem "消费金额"
      Combo3.AddItem "余额"
      Combo3.AddItem "备注"
      
      Combo4.AddItem "="
      Combo4.AddItem "<"
      Combo4.AddItem ">"
      Combo4.AddItem "<>"
      
      Combo5.AddItem "="
      Combo5.AddItem "<"
      Combo5.AddItem ">"
      Combo5.AddItem "<>"
      
      Combo6.AddItem "="
      Combo6.AddItem "<"
      Combo6.AddItem ">"
      Combo6.AddItem "<>"
      
      Combo7.AddItem "与"
      Combo7.AddItem "或"
      
      Combo8.AddItem "与"
      Combo8.AddItem "或"
      frmOnlstat.Width = 13630
      frmOnlstat.Height = 9680
End Sub

    （2） 定义一个函数field，将汉语转化为数据库可以识别的语言。

 Public Function field(comboX As String) As String
      Select Case comboX
            Case "卡号"
                field = "cardno"
            Case "姓名"
               field = "studentName"
            Case "上机日期"
               field = "ondate"
            Case "上机时间"
               field = "ontime"
            Case "下机日期"
               field = "offdate"
            Case "下机时间"
               field = "offtime"
            Case "消费金额"
               field = "consume"
            Case "余额"
               field = "cash"
            Case "备注"
               field = "status"
            Case "与"
               field = "and"
            Case "或"
               field = "or"
       End Select
End Function

  （3） 定义一个cancel过程：

 Public Function Cancel()
       Combo1.Text = ""
       Combo2.Text = ""
       Combo3.Text = ""
       Combo4.Text = ""
       Combo5.Text = ""
       Combo6.Text = ""
       Combo7.Text = ""
       Combo8.Text = ""
       Text1.Text = ""
       Text2.Text = ""
    With MSHFlexGrid1
         .Rows = 2
         .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) = ""
         .TextMatrix(1, 0) = ""
         .TextMatrix(1, 1) = ""
         .TextMatrix(1, 2) = ""
         .TextMatrix(1, 3) = ""
         .TextMatrix(1, 4) = ""
         .TextMatrix(1, 5) = ""
         .TextMatrix(1, 6) = ""
         .TextMatrix(1, 7) = ""
         .TextMatrix(1, 8) = ""
    End With
End Function

（4）查询按钮的单击事件：

Private Sub cmdInquire_Click()
    Dim ctrl As Control
    Dim mrc As ADODB.Recordset
    Dim txtSQL As String
    Dim MsgText As String
    Dim i As Integer
    
    If Combo7.Text = "" Then  '没有组合关系
    
       If Trim(Text1.Text) = "" Or Trim(Combo1.Text) = "" Or Trim(Combo4.Text) = "" Then
          MsgBox "请输入完整的查询条件", , "提示"
          Exit Sub
       Else  '不为空，调出相关信息
          txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'"
       End If
    Else
        
        If Combo8.Text = "" Then  '有一个组合关系
           '不能为空
           If Trim(Text2.Text) = "" Or Trim(Combo2.Text) = "" Or Trim(Combo5.Text) = "" Then
              MsgBox "请输入完整的查询条件", , "提示"
              Exit Sub
           Else
              txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" _
              & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(Text2.Text) & "'"
           End If
        
        Else '不为空，有两个组合关系
           
           If Trim(Text3.Text) = "" Or Trim(Combo3.Text) = "" Or Trim(Combo6.Text) = "" Then
              MsgBox "请输入完整的查询条件", , "提示"
              Exit Sub
           Else
              txtSQL = "select * from Line_info where " & Trim(field(Combo1.Text)) & Trim(Combo4.Text) & "'" & Trim(Text1.Text) & "'" _
              & " " & Trim(field(Combo7.Text)) & " " & Trim(field(Combo2.Text)) & Trim(Combo5.Text) & "'" & Trim(field(Text2.Text)) & "'" _
              & " " & Trim(field(Combo8.Text)) & " " & Trim(field(Combo3.Text)) & Trim(Combo6.Text) & "'" & Trim(Text3.Text) & "'"
           End If
        
        End If
    
    End If
           
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    If mrc.EOF = True Then '检查信息是否存在，如果不存在给出提示并清空所有文本框
        MsgBox "没有查询到结果，请检查输入信息！", vbOKOnly + vbExclamation, "提示"
        Call Cancel
        Exit Sub
    End If
    With MSHFlexGrid1
        .Rows = 1
        .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
            .TextMatrix(.Rows - 1, 0) = mrc.Fields(1).Value & ""
            .TextMatrix(.Rows - 1, 1) = mrc!studentname
            .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6).Value & "")
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7).Value & "")
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8).Value & "")
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(9).Value & "")
            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(10).Value & "")
            .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(11).Value & "")
            .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(12).Value & "")
            mrc.MoveNext
        Loop
    End With
    mrc.Close
end sub

       现在看来组合查询也没有什么难的，只是多了一些条件而已，将这些条件连接起来就可以了。有的时候觉得不要看别人的代码要自己完成，但也不能自己闷头自己敲，别人的代码页是可以借鉴的，就像我一样自己敲组合查询的时候用了好久，写了好多代码，容易出错，也调试了好多，看了别人的代码了解别人的逻辑使自己以后的组合查询少走很多弯路。看与不看要掌握一个度，这个度要靠自己去衡量。