机房收费系统——组合查询 1.0

最新推荐文章于 2025-08-06 16:23:50 发布

LLLDa_&

最新推荐文章于 2025-08-06 16:23:50 发布

阅读量526

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/Ming_5257/article/details/78822033

机房收费系统专栏收录该内容

8 篇文章

订阅专栏

本文详细解析了在一个机房收费系统中实现复杂组合查询的过程。通过三种不同的查询条件组合，介绍了如何构造SQL查询语句，并提供了完整的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、引言

组合查询这个问题是我机房收费系统遇到的第一个难题！而且困扰了我很久！
主要有两个问题：
第一是逻辑上的问题！
第二是数据库查询上的问题！
接下来就说说这两个问题。

二、组合逻辑

学生基本信息组合查询
一共有三组查询条件，两个组合！
难点：用户选择选择条件及其组合情况的判断
最简单的解决办法就算是按照正常的逻辑，一条一条的进行判断！
简单的画了一个流程图！
组合条件判断
这个流程非常容易理解！代码也很容易实现！

三、组合查询语句

1、只有一个条件的时候的查询

txtSQL = "select * from student_info where " & field(Trim(ziduancombo1.Text)) & Trim(caozuofucombo1.Text) & "'" & Trim(txtchaxun1.Text) & "'"

注：field为用来转换combo中的选项，将他们与数据库中的列名对应起来！
field用之前需要定义！

Public Function field(i As String) As String
Select Case ziduancombo1.Text
    Case "卡号"
field = "cardno"
    Case "学号"
field = "studentno"
    Case "姓名"
field = "studentname"
    Case "性别"
field = "sex"
    Case "班级"
field = "class"
    Case "年级"
field = "grade"
    Case "系别"
field = "department"
    Case "日期"
field = "date"
    Case "时间"
field = "time"                        
End Function

2、两个条件进行组合查询语句

txtSQL = txtSQL & strzuhecombo1 & " " & field(Trim(ziduancombo2.Text)) & Trim(caozuofucombo2.Text) & "'" & Trim(txtchaxun2.Text) & "'"

注：strzuhecombo1需要定义，用来转换“或”和“与”，使之能够被数据库识别

  Select Case zuhecombo1.Text
      Case "与"
         strzuhecombo1 = "and"
      Case "或"
             strzuhecombo1 = "or"
  End Select

3、三个条件进行组合查询语句

txtSQL = txtSQL & strzuhecombo2 & " " & field(Trim(ziduancombo3.Text)) & Trim(caozuofucombo3.Text) & "'" & Trim(txtchaxun3.Text) & "'"

四、整个过程代码

Private Sub cmdok_Click()
If Trim(ziduancombo1.Text) = "" Then
    MsgBox "请选择字段！", vbOKOnly + vbExclamation, "警告"
    ziduancombo1.SetFocus
Else
    If Trim(caozuofucombo1.Text) = "" Then
        MsgBox "请选择操作符！", vbOKOnly + vbExclamation, "警告"
        caozuofucombo1.SetFocus
    Else
        If Trim(txtchaxun1.Text) = "" Then
            MsgBox "请输入要查询的内容！", vbOKOnly + vbExclamation, "警告"
            txtchaxun1.SetFocus
            Exit Sub
        Else
            '执行查询
             txtSQL = "select * from student_info where " & field(Trim(ziduancombo1.Text)) & Trim(caozuofucombo1.Text) & "'" & Trim(txtchaxun1.Text) & "'"
            '判断第一个组合条件时为空
            If Trim(zuhecombo1.Text) = "" Then '此时没有组合查询
            Set mrc = executeSQL(txtSQL, msgtext)
                If mrc.EOF = True Then
                    MsgBox "没有该记录！", vbOKOnly + vbExclamation, "提示"
                    Exit Sub
                End If
            Else
                '选择了组合条件1时进行组合查询

                    If Trim(ziduancombo2.Text) = "" Then
                        MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"
                        ziduancombo2.SetFocus
                    Else
                        If Trim(caozuofucombo2.Text) = "" Then
                            MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"
                            caozuofucombo2.SetFocus
                        Else
                            If Trim(txtchaxun2.Text) = "" Then
                                MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"
                                txtchaxun2.SetFocus
                                Exit Sub
                            Else
                                If Trim(zuhecombo2.Text) = "" Then '第二个组合条件为空，进行前两个的组合查询
                                '执行查询
                                Dim strzuhecombo1, strzuhecombo2 '定义变量用来转换与和或
                                If zuhecombo1.Text <> "" Then
                                 Select Case zuhecombo1.Text
                                  Case "与"
                                   strzuhecombo1 = "and"
                                  Case "或"
                                   strzuhecombo1 = "or"
                                End Select
                                End If
                                 txtSQL = txtSQL & strzuhecombo1 & " " & field(Trim(ziduancombo2.Text)) & Trim(caozuofucombo2.Text) & "'" & Trim(txtchaxun2.Text) & "'"
                                Set mrc = executeSQL(txtSQL, msgtext)
                                    If mrc.EOF = True Then
                                        MsgBox "没有此条记录！", vbOKOnly + vbExclamation, "提示"
                                        ziduancombo1.Text = ""
                                        ziduancombo2.Text = ""
                                        caozuofucombo1.Text = ""
                                        caozuofucombo2.Text = ""
                                        txtchaxun1.Text = ""
                                        txtchaxun2.Text = ""
                                        zuhecombo1.Text = ""
                                        Exit Sub
                                     End If
                                Else            '如果第二个组合条件不为空时，对第三行的条件进行检查
                                    If Trim(ziduancombo3.Text) = "" Then
                                        MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"
                                        ziduancombo3.SetFocus
                                    Else
                                        If Trim(caozuofucombo3.Text) = "" Then
                                            MsgBox "请将查询条件填写完整", vbOKOnly + vbExclamation, "提示"
                                            caozuofucombo3.SetFocus
                                        Else
                                            If Trim(txtchaxun3.Text) = "" Then
                                                MsgBox "请将查询条件填写完整！", vbOKOnly + vbExclamation, "提示"
                                                txtchaxun3.SetFocus
                                                Exit Sub
                                            Else    '都满足条件后，进行组合查询！
                                            Select Case zuhecombo2.Text
                                                Case "与"
                                                    strzuhecombo2 = "and"
                                                Case "或"
                                                    strzuhecombo2 = "or"
                                            End Select
                                                txtSQL = txtSQL & strzuhecombo2 & " " & field(Trim(ziduancombo3.Text)) & Trim(caozuofucombo3.Text) & "'" & Trim(txtchaxun3.Text) & "'"
                                                Set mrc = executeSQL(txtSQL, msgtext)
                                                If mrc.EOF = True Then
                                                    MsgBox "没有此条查询记录！", vbOKOnly + vbExclamation, "提示"
                                                    ziduancombo1.Text = ""
                                                    ziduancombo2.Text = ""
                                                    ziduancombo3.Text = ""
                                                    caozuofucombo1.Text = ""
                                                    caozuofucombo2.Text = ""
                                                    caozuofucombo3.Text = ""
                                                    txtchaxun1.Text = ""
                                                    txtchaxun2.Text = ""
                                                    txtchaxun3.Text = ""
                                                    zuhecombo1.Text = ""
                                                    zuhecombo2.Text = ""
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
            End If
        End If
    End If
End If
'显示查询结果
            With MSFgxinxichaxun
         .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