机房收费系统之组合查询简化版-基本信息维护窗体（采用SQL交查询、并查询语句进行组合查询）

       第一次接触组合查询时感觉很内容很复杂，逻辑梳理就花费了很长时间，在第一个‘学生上机统计信息’的组合查询窗体中有很多代码冗余，导致代码篇幅很长，这次在做‘学生基本信息维护’窗体时又遇到了组合查询，这次的组合查询并未直接按照之前的那种方法，因为那个太长了，这次自己又研究出一个新的方法，即用SQL的交集、并集语句来进行组合查询，然而效果也是很明显的，代码量减少了很多，一眼就可以看出是什么逻辑。

一、基本信息维护窗体流程图

二、查询集

SQL的交集，并集

https://blog.csdn.net/Elsa15/article/details/81118256

三、代码片段

①组合查询片段

Private Sub cmdQuery_Click()
    Dim mrc As ADODB.Recordset
    Dim txtSQL As String
    Dim MsgText As String
    Dim g1mrc As ADODB.Recordset
    Dim g1txtSQL As String
    Dim g1MsgText As String
    Dim g2mrc As ADODB.Recordset
    Dim g2txtSQL As String
    Dim g2MsgText As String
    
    '各字段查询语句
    txtSQL = "select * from student_Info where " & fieldname(combField(0).Text) & "" & combOperator(0).Text & "'" & Trim(txtQuery0.Text) & "'"
    g1txtSQL = "select * from student_Info where " & fieldname(combField(1).Text) & "" & combOperator(1).Text & "'" & Trim(txtQuery1.Text) & "'"
    g2txtSQL = "select * from student_Info where " & fieldname(combField(2).Text) & "" & combOperator(2).Text & "'" & Trim(txtQuery2.Text) & "'"

    '设置字段一查询条件
    If Not testtxt(combGroup(0)) Then
    txtSQL = txtSQL
    Set mrc = ExecuteSQL(txtSQL, MsgText)
    End If
        
    '设置字段二查询条件及赋予记录集数据
    If testtxt(combGroup(0)) Then
'    g1txtSQL = txtSQL & fieldname(combGroup(0)) & g1txtSQL
    g1txtSQL = "(select * from student_Info where " & fieldname(combField(0).Text) & "" & combOperator(0).Text & "'" & Trim(txtQuery0.Text) & "'" & fieldname(combGroup(0)) & "select * from student_Info where " & fieldname(combField(1).Text) & "" & combOperator(1).Text & "'" & Trim(txtQuery1.Text) & "')"
    Debug.Print g1txtSQL
    Set g1mrc = ExecuteSQL(g1txtSQL, g1MsgText)
    End If

    '设置有字段二前提下字段三的查询条件及赋予记录集数据
    If testtxt(combGroup(0)) And testtxt(combGroup(1)) Then
    g2txtSQL = g1txtSQL & fieldname(combGroup(1)) & g2txtSQL
    Debug.Print g2txtSQL
    Set g2mrc = ExecuteSQL(g2txtSQL, g2MsgText)
    End If
    
    '设置无字段二前提下字段三的查询条件及赋予记录集数据
    If Not testtxt(combGroup(0)) And testtxt(combGroup(1)) Then
    g2txtSQL = txtSQL & fieldname(combGroup(1)) & g2txtSQL
    Set g2mrc = ExecuteSQL(g2txtSQL, g2MsgText)
    End If
    
    '设置变量记录集数据内容
    If Not testtxt(combGroup(0)) Then
       Set mrc = mrc
    Else
       Set mrc = g1mrc
    End If
    If testtxt(combGroup(1)) Then
       Set mrc = g2mrc
    End If

    '加载数据至myflexgrid控件
    With myflexgrid
        .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) = "状态"
        .TextMatrix(0, 9) = "备注"
        .TextMatrix(0, 10) = "类型"
        .TextMatrix(0, 11) = "日期"
        .TextMatrix(0, 12) = "时间"
        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(0))
            .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
            .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
            .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))
            .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))
            .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))
            .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))
            .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))
            .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))
            .TextMatrix(.Rows - 1, 11) = Trim(mrc.Fields(12))
            .TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(13))
            mrc.MoveNext
        Loop
    End With
End Sub

② 打开窗体时的加载内容

Private Sub Form_Load()
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    
    '打开窗体时的加载内容
    For i = 0 To 2
        combField(i).AddItem "卡号"
        combField(i).AddItem "学号"
        combField(i).AddItem "姓名"
        combField(i).AddItem "性别"
        combField(i).AddItem "系别"
        combField(i).AddItem "年级"
        combField(i).AddItem "班级"
    Next
    
    For j = 0 To 2
        combOperator(j).AddItem ">"
        combOperator(j).AddItem "<"
        combOperator(j).AddItem "="
        combOperator(j).AddItem "<>"
    Next j
    
    For n = 0 To 1
        combGroup(n).AddItem "与"
        combGroup(n).AddItem "或"
    Next n
    
End Sub

  ③ 组合框文本内容的转换

Public Function fieldname(strfieldname As String) As String
    Select Case strfieldname           '组合框文本内容的转换
    Case "卡号"
        fieldname = "cardno"
    Case "学号"
        fieldname = "studentNo"
    Case "姓名"
        fieldname = "studentName"
    Case "性别"
        fieldname = "sex"
    Case "系别"
        fieldname = "department"
    Case "年级"
        fieldname = "grade"
    Case "班级"
        fieldname = "class"
    Case "与"
        fieldname = " intersect "
    Case "或"
        fieldname = " union "
    End Select
    
End Function

④清空组合框和文本框内容

Private Sub cmdclear_Click()
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    
    '清空
    For i = 0 To 2
        combField(i).Text = ""
        combField(i).Text = ""
        combField(i).Text = ""
        combField(i).Text = ""
        combField(i).Text = ""
        combField(i).Text = ""
        combField(i).Text = ""
    Next
    
    For j = 0 To 2
        combOperator(j).Text = ""
        combOperator(j).Text = ""
        combOperator(j).Text = ""
        combOperator(j).Text = ""
    Next j
    
    For n = 0 To 1
        combGroup(n).Text = ""
        combGroup(n).Text = ""
    Next n
    
    txtQuery0.Text = ""
    txtQuery1.Text = ""
    txtQuery2.Text = ""
End Sub

 

     看完这篇博客的代码再看上次总结的代码（机房收费系统之组合查询（代码部分））还是很开心的，上篇的博客只发了查询部分就有那么长的代码，这次是整个窗体的所有功能代码。组合查询的代码终于简化了，重复的力量真的很强大，这次简化版的代码应该还有很多的冗余或者不足的地方，不过对现阶段的我来讲已经OK了，相信后面的学习中还会再遇到组合查询，相信那时又会有新的思路，不断优化嘛，下一站学生上机统计信息查询窗体走起^_^