组合查询——拼接字符串

﹏ヽ暗。殇╰゛Y 2022-08-27 10:44 200阅读 0赞

首先，什么情况下要用到组合查询呢？总的来说，有两种情况：一是在单个查询中从不同的表返回类似结构的数据；二是对单个表执行多个查询，按单个查询返回数据。在这里，我们说的组合查询是指第二种情况，即要查询的表是固定的，查询条件是不定的并且有多个查询条件。

从例子来看，

![SouthEast][]

例如，输入相应的查询条件

1、教师不等于“0”

2、机器号等于“yang”

两个条件是“与”的关系，我们可以很容易的写出它的sql语句

select \* from T\_Worklog\_Info  where id <> '0' and computer='yang'

所以接下来要做的核心就是拼出这条语句。

在U层，我们把输入的查询条件的信息赋给实体，然后把实体传入B层。

Dim student As New Login.Model.CmbQueryInfo
            Dim Bquery As New Login.BLL.cmbQueryforBLL
            '定义控件基类
            Dim controlArray(2) As System.Windows.Forms.Control
            Dim table As New DataTable
            Dim i As Integer
    
            controlArray(0) = cmbWord1
            controlArray(1) = cmbOperator1
            controlArray(2) = txtContent1
            '字段名
            student.FileName1 = cmbWord1.Text
            student.FileName2 = cmbWord2.Text
            student.FileName3 = cmbWord3.Text
            '操作符
            student.Operator1 = cmbOperator1.Text
            student.Operator2 = cmbOperator2.Text
            student.Operator3 = cmbOperator3.Text
            '查询内容
            student.QueryContent1 = txtContent1.Text
            student.QueryContent2 = txtContent2.Text
            student.QueryContent3 = txtContent3.Text
            '组合关系
            student.CompositionRelation1 = cmbRelation1.Text
            student.CompositionRelation2 = cmbRelation2.Text
    
            DataGridView1.Rows.Clear()
            '查询
            table = Bquery.cmbQuery(student, "T_Worklog_Info")

在B层，接收实体之后，开始拼接字符串。

首先，会用到两个方法。

ModifyFields方法主要是把“卡号”等中文字符转换为数据库中的字段“cardno”。

Public Function ModifyFields(student As Login.Model.CmbQueryInfo) As Login.Model.CmbQueryInfo
            Dim str As String
            Select Case student.FileName1
                Case "卡号"
                    str = "cardno"
                Case "学号"
                    str = "studentno"
                Case "姓名"
                    str = "studentname"
                Case "性别"
                    str = "sex"
                Case "系别"
                    str = "apartment"
                Case "年级"
                    str = "grade"
                Case "班级"
                    str = "class"
                Case "上机日期"
                    str = "ondate"
                Case "上机时间"
                    str = "ontime"
                Case "下机日期"
                    str = "offdate"
                Case "下机时间"
                    str = "offtime"
                Case "机房号"
                    str = "computer"
                Case "机器号"
                    str = "computer"
                Case "登录日期"
                    str = "LoginDate"
                Case "登录时间"
                    str = "LoginTime"
                Case "注销日期"
                    str = "LogoutDate"
                Case "注销时间"
                    str = "LogoutTime"
                Case "教师"
                    str = "id"
                Case Else
                    str = ""
            End Select
            student.FileName1 = str
    
            'student.FileName2, student.FileName3类似
    
            Select Case student.CompositionRelation1
                Case "与"
                    str = "and"
                Case "或"
                    str = "or"
                Case Else
                    str = "NO"
            End Select
            student.CompositionRelation1 = str
            Select Case student.CompositionRelation2
                Case "与"
                    str = "and"
                Case "或"
                    str = "or"
                Case Else
                    str = "NO"
            End Select
            student.CompositionRelation2 = str
            Return student
        End Function

CreateQueryContent方法

Public Function CreateQueryContent(student As Login.Model.CmbQueryInfo) As Login.Model.CmbQueryInfo
            Dim tempQueryComposition As New Login.Model.CmbQueryInfo
            Try
                tempQueryComposition.QueryContent1 = student.FileName1 + " " + student.Operator1 + "'" + student.QueryContent1 + "'"
                tempQueryComposition.QueryContent2 = student.FileName2 + " " + student.Operator2 + _
                                                  "'" + student.QueryContent2 + "'"
                tempQueryComposition.QueryContent3 = student.FileName3 + " " + student.Operator3 + _
                                                "'" + student.QueryContent3 + "'"
    
                tempQueryComposition.CompositionRelation1 = student.CompositionRelation1
                tempQueryComposition.CompositionRelation2 = student.CompositionRelation2
                Return tempQueryComposition
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Function

在D层，完成最后的sql语句的拼接。

Public Function CreateSqlString(student As Login.Model.CmbQueryInfo, tablename As String) As String
            Dim sql As String
    
            Try
                '没有组合关系时
                If student.CompositionRelation1.Trim = "NO" Then
    
                    sql = "select * from " + tablename + " Where" + " " + student.FileName1 + student.Operator1 + "'" + student.QueryContent1 + "'"
                Else
                    '有一个组合关系时
                    sql = "select * from " + tablename + " Where" + " " + student.FileName1 + student.Operator1 + "'" + student.QueryContent1 + "'" + " " + student.CompositionRelation1 + " " + student.FileName2 + student.Operator2 + "'" + student.QueryContent2 + "'"
                End If
                '有两个组合关系时
                If student.CompositionRelation2.Trim <> "NO" Then
                    sql = sql + student.CompositionRelation2 + " " + student.FileName3 + student.Operator3 + "'" + student.QueryContent3 + "'"
                End If
    
                Return sql
            Catch ex As Exception
    
            End Try
        End Function

拼接完成后，可以放到数据库中检验一下，接下来的问题就容易多了。

最后的显示结果：

![SouthEast 1][]

组合查询，其本质终究还是查询。用拼接字符串的方法理解起来比较容易，但特别容易出错。一不小心sql语句就

会出错，不过调试起来也挺容易发现错误的。期待大家与我交流其他的查询方法。

[SouthEast]: /images/20220824/15389af5c7ba4257b203cab4281d842d.png
[SouthEast 1]: /images/20220824/bd9b55654d384d3b9c2765b538f6022f.png