首先,什么情况下要用到组合查询呢?总的来说,有两种情况:一是在单个查询中从不同的表返回类似结构的数据;二是对单个表执行多个查询,按单个查询返回数据。在这里,我们说的组合查询是指第二种情况,即要查询的表是固定的,查询条件是不定的并且有多个查询条件。
从例子来看,
例如,输入相应的查询条件
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
本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/webkf/aspx/
以上是小编为您精心准备的的内容,在的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索查询
, text
, filename
, case
, student
, str
, 两个等于号
组合拼接字符串
mysql查询拼接字符串、sql查询拼接字符串、oracle查询拼接字符串、mysql 拼接字符串、sql字符串拼接,以便于您获取更多的相关知识。