现在的位置: 首页 > 综合 > 正文

基础很重要,查询如何“组合”!谈谈组合查询

2013年03月11日 ⁄ 综合 ⁄ 共 4451字 ⁄ 字号 评论关闭

    前言:

    在之前是没有接触过组合查询的。一听说,哇!组合查询!什么?没有听过,第一个反应。不会,哇塞,肯定好难。听别人的一说,哦!更加明白了,我做不出来!呵呵,这样一个心态。我开始了“组合”查询。后来当然是完成了,但我发现事情远远没有做完那么简单!

首先,还是先说说这些组合查询吧!

如下图,红色框的内容利用蓝色框的关系与下面黄色框的内容结合。进而来返回表中的数据

利用数组做为每个字段名的变量,这个问题就迎刃而解了。请看如下代码

第一部分:使文本框合法输入,并利用变量将字段内容取出来

    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

抱歉!评论已关闭.