机房收费系统之组合查询

 组合查询相对来说就是繁琐一点，下面就是我对组合查询的一些理解：

首先要做的就是：先理清里面的关系。

    我将上面的查询条件分成三个部门，分别为：第一查询条件，第二查询条件，第三查询条件；中间以组合关系为分界线。

    其次，将字段名及组合关系中的内容转换成数据库中相对应的字段及关键字。这时就要用到过程：

Private Sub QueryCondition2()
    '将字段2转成sql里与之对应的字段名
    Select Case Combo4
        Case "卡号"
        ZiDuan(1) = "cardID"
        Case "姓名"
        ZiDuan(1) = "studentName"
        Case "上机日期"
        ZiDuan(1) = "OnboardDate"
        Case "上机时间"
        ZiDuan(1) = "OnboardTime"
        Case "下机日期"
        ZiDuan(1) = "OffboardDate"
        Case "下机时间"
        ZiDuan(1) = "OffboardTime"
        Case "消费金额"
        ZiDuan(1) = "OnboardCost"
        Case "余额"
        ZiDuan(1) = "RemainMoney"
        Case "备注"
        ZiDuan(1) = "BeiZhu"
    End Select
    
    '转义sql连接字符1
    Select Case Combo3
        Case "与"
        ZuHeRelation(0) = "and"
        Case "或"
        ZuHeRelation(0) = "or"
    End Select

End Sub

    其余的转义条件也是一样的，这里就不在累赘。有了上面的查询条件，最后就是将这些条件连接起来组成查询语句：    

Private Sub SQL()
    '查询条件函数
    txtSQL = "select * from studentOnboardRecord_Info where "
	
	‘判断查询条件1
		'判断字段不能为空
    If Combo1.Text = "" Then
        MsgBox "字段不能为空", vbOKOnly + vbExclamation, "提示"
        Combo1.SetFocus
        Exit Sub
    End If
		‘判断操作符不能为空
    If Combo2.Text = "" Then
        MsgBox "操作符不能为空", vbOKOnly + vbExclamation, "提示"
        Combo2.SetFocus
        Exit Sub
    End If
		’判断查询内容不能为空
    If Text1.Text = "" Then
        MsgBox "查询内容不能为空", vbOKOnly + vbExclamation, "提示"
        Text1.SetFocus
        Exit Sub
    End If
    
    Call QueryCondition1
    
	’一二查询语句分界线语句，为空则查询语句为第一查询条件，不为空则继续判断第二查询条件
    If Combo3.Text = "" Then
        txtSQL = txtSQL & " " & Trim(ZiDuan(0)) & "  " & Trim(Combo2.Text) & " '" & Trim(Text1.Text) & "'"
    Else
		‘判断第二查询条件
			’字段不能为空
        If Combo4.Text = "" Then
            MsgBox "字段不能为空", vbOKOnly + vbExclamation, "提示"
            Combo4.SetFocus
            Exit Sub
        End If
			‘操作符不能为空
        If Combo5.Text = "" Then
            MsgBox "操作符不能为空", vbOKOnly + vbExclamation, "提示"
            Combo5.SetFocus
            Exit Sub
        End If
			’查询内容不能为空
        If Text2.Text = "" Then
            MsgBox "查询内容不能为空", vbOKOnly + vbExclamation, "提示"
            Text2.SetFocus
            Exit Sub
        End If
        ‘调用查询条件2
        Call QueryCondition2
        
		‘二三查询条件分界线
        If Combo6.Text = "" Then
            txtSQL = txtSQL & " " & Trim(ZiDuan(0)) & "  " & Trim(Combo2.Text) & " '" & Trim(Text1.Text) & "' " & Trim(ZuHeRelation(0)) & " " & Trim(ZiDuan(1)) & "  " & Trim(Combo5.Text) & " '" & Trim(Text2.Text) & "'"
        Else
			’判断查询条件3
				‘字段不能为空
            If Combo7.Text = "" Then
                MsgBox "字段不能为空", vbOKOnly + vbExclamation, "提示"
                Combo7.SetFocus
                Exit Sub
            End If
				’操作符不能为空
            If Combo8.Text = "" Then
                MsgBox "操作符不能为空", vbOKOnly + vbExclamation, "提示"
                Combo8.SetFocus
                Exit Sub
            End If
				‘查询内容不能为空
            If Text3.Text = "" Then
                MsgBox "查询内容不能为空", vbOKOnly + vbExclamation, "提示"
                Text3.SetFocus
                Exit Sub
            End If
            
			’调用查询条件3
            Call QueryCondition3
            txtSQL = txtSQL & " " & Trim(ZiDuan(0)) & "  " & Trim(Combo2.Text) & " '" & Trim(Text1.Text) & "' " & Trim(ZuHeRelation(0)) & " " & Trim(ZiDuan(1)) & "  " & Trim(Combo5.Text) & " '" & Trim(Text2.Text) & "' " & Trim(ZuHeRelation(1)) & " " & Trim(ZiDuan(2)) & "  " & Trim(Combo8.Text) & " '" & Trim(Text3.Text) & "'"
        End If
    End If
End Sub

 

    通过上面的过程而形成sql查询语句过程，剩下的只要在查询事件中调用sql查询语句过程即可。

    这样做使得在查询事件中代码量少而且清晰，简洁，一看就懂，很容易帮助我们快速的理解程序。