vb.net 下的sql多表查询问题

来源：互联网发布：为什么联通4g网络很差编辑：程序博客网时间：2024/05/24 11:13

前一阵子在做一个关于配件维修程序的项目
技术选型为vb.Net 和access数据库
数据库的结构是这样的
分别有客户表，机器表，配件表三个基本信息表
将客户和机器信息对应形成客户机器表，为客户机器表中的机器添加配件形成机器配件表，像机器配件表里加维修时间形成配件维修表
在一个datagridview中我首先想实现的功能是统计客户的机器数
sql 语句是这么写的

</pre><pre name="code" class="sql">select 客户表.ID as ID,客户表.姓名,count(机器客户表.ID) as 机器数,客户表.电话 as 客户电话,客户表.手机 as 客户手机,客户表.联系地址 as 客户地址,客户表.备注 as 备注

from 客户表  left join 机器客户表 on  客户表.ID=机器客户表.客户号  group by 客户表.ID,客户表.姓名,客户表.电话,客户表.手机,客户表.联系地址,客户表.备注

而后再连表中join 配件表后，随着记录的增加，机器数目将会发生变化

而且accees 不支持distinct 函数

然后我采取的解决方案是

 Public Function willPjCount(ByVal khId As String) As String '返回即将维修配件数量        On Error GoTo ErrorHandler        Dim con = New OleDbConnection(Autumn_CString1)        Dim Sql As String        willPjCount = 0        Sql = "select sum(iif(DateDiff('d',date(),配件机器表.提醒时间)>0 and DateDiff('d',date(),配件机器表.提醒时间)<配件表.预警时间, 1,0)) as 即将维修配件数"        ' ''sqlshowJqData = sqlshowJqData & "机器客户表.备注 as 备注"        Sql = Sql & " from 客户表,机器客户表,配件机器表,配件表 where 客户表.ID=机器客户表.客户号 and 配件机器表.机器ID = 机器客户表.ID  and 配件表.ID =配件机器表.配件ID " & khId        con.Open()               Dim cmd = New OleDbCommand(Sql, con)        Dim myreader As OleDbDataReader = cmd.ExecuteReader        While myreader.Read            If myreader("即将维修配件数").GetType.ToString() = "System.Double" Then                willPjCount = myreader("即将维修配件数")            End If        End While        cmd.Dispose()        con.Close()        Exit FunctionErrorHandler:        MsgBox("错误号:" & Err.Number & vbCrLf & +Err.Description, vbExclamation + vbOKOnly, "其他错误!")        Exit Function    End Function

 Public Function OutPjCount(ByVal khId As String) As String '返回过期维修配件数量        On Error GoTo ErrorHandler        Dim con = New OleDbConnection(Autumn_CString1)        Dim Sql As String        OutPjCount = 0        Sql = "select sum(iif(DateDiff('d',date(),配件机器表.提醒时间)<0 , 1,0)) as 过期维修配件数"        ' ''sqlshowJqData = sqlshowJqData & "机器客户表.备注 as 备注"        Sql = Sql & " from 客户表,机器客户表,配件机器表 where 客户表.ID=机器客户表.客户号 and 配件机器表.机器ID = 机器客户表.ID   " & khId        con.Open()        Dim cmd = New OleDbCommand(Sql, con)        Dim myreader As OleDbDataReader = cmd.ExecuteReader        While myreader.Read            OutPjCount = myreader("过期维修配件数")        End While        cmd.Dispose()        con.Close()        Exit FunctionErrorHandler:        'MsgBox("错误号:" & Err.Number & vbCrLf & "错误内容:系统基本信息" & khId & "设置不完整，请添加供应商名称", vbExclamation + vbOKOnly, "其他错误!")        Exit Function    End Function

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">而后采取的解决方案是在myread里二次读取</span>

<span style="font-family: Arial, Helvetica, sans-serif;"> </span><span style="font-family: Arial, Helvetica, sans-serif;"> willPj = willPjCount("and 客户表.ID =" & myreader("ID"))</span>

   outPj = OutPjCount("and 客户表.ID =" & myreader("ID"))    .Rows(i).Cells(4).Value = willPj   .Rows(i).Cells(5).Value = outPj    If Convert.ToInt32(willPj) > 0 Then      .Rows(i).Cells(4).Style.BackColor = Color.Red   End If    If Convert.ToInt32(outPj) > 0 Then    .Rows(i).Cells(5).Style.BackColor = Color.Blue    End If    .Rows(i).Cells(6).Value = myreader("客户电话")    .Rows(i).Cells(7).Value = myreader("客户手机")    .Rows(i).Cells(8).Value = myreader("客户地址")      .Rows(i).Cells(9).Value = myreader("备注")

但在记录数超过20个时，就会出线未知的错误

最后的解决方案

Dim cmd = New OleDbCommand(Sql, con)            Dim myreader As OleDbDataReader = cmd.ExecuteReader            Dim i As Integer            Dim idI As New Dictionary(Of String, Integer)            Dim outPj As String = ""            Dim willPj As String = ""            Dim tempId As Integer            Dim maxId As Integer            i = 0            With datagridview_t                .Columns.Clear()                客户信息列表头初始化(datagridview_t)                While (myreader.Read)                    .Rows.Add()                    .Rows(i).Cells(1).Value = myreader("ID")                    idI.Add(myreader("ID"), i)                                      .Rows(i).Cells(2).Value = myreader("姓名")                    .Rows(i).Cells(3).Value = myreader("机器数")                                     .Rows(i).Cells(6).Value = myreader("客户电话")                    .Rows(i).Cells(7).Value = myreader("客户手机")                    .Rows(i).Cells(8).Value = myreader("客户地址")                    .Rows(i).Cells(9).Value = myreader("备注")                    i = i + 1                End While                maxId = i - 1                Sql = "select   客户表.ID as ID,sum(iif(DateDiff('d',date(),配件机器表.提醒时间)>-7 and DateDiff('d',date(),配件机器表.提醒时间)<配件表.预警时间, 1,0)) as 即将维修配件数,sum(iif(DateDiff('d',date(),配件机器表.提醒时间)<-7 , 1,0))  as 过期维修配件数"                             Sql = Sql & " from 客户表,机器客户表,配件机器表,配件表 where 客户表.ID=机器客户表.客户号 and 配件机器表.机器ID = 机器客户表.ID  and 配件表.ID =配件机器表.配件ID     group by 客户表.ID"                cmd = New OleDbCommand(Sql, con)                myreader = cmd.ExecuteReader                i = 0                While (myreader.Read)                    tempId = i                    If tempId <> 0 Then                        tempId += 1                    End If                    i = idI(myreader("ID"))                    For j = tempId To i - 1                        .Rows(j).Cells(4).Value = 0                        .Rows(j).Cells(5).Value = 0                    Next                    .Rows(i).Cells(4).Value = Convert.ToInt32(myreader("即将维修配件数"))                    .Rows(i).Cells(5).Value = Convert.ToInt32(myreader("过期维修配件数"))                    If .Rows(i).Cells(4).Value > 0 Then                        .Rows(i).Cells(4).Style.BackColor = Color.Red                    End If                    If .Rows(i).Cells(5).Value > 0 Then                        .Rows(i).Cells(5).Style.BackColor = Color.Blue                    End If                    willPj = willPjCount("and 客户表.ID =" & myreader("ID"))                    outPj = OutPjCount("and 客户表.ID =" & myreader("ID"))                    .Rows(i).Cells(4).Value = willPj                    .Rows(i).Cells(5).Value = outPj                    If Convert.ToInt32(willPj) > 0 Then                        .Rows(i).Cells(4).Style.BackColor = Color.Red                    End If                    If Convert.ToInt32(outPj) > 0 Then                        .Rows(i).Cells(5).Style.BackColor = Color.Blue                    End If                    .Rows(i).Cells(6).Value = myreader("客户电话")                    .Rows(i).Cells(7).Value = myreader("客户手机")                    .Rows(i).Cells(8).Value = myreader("客户地址")                    .Rows(i).Cells(9).Value = myreader("备注")                End While                For j = i + 1 To maxId                    .Rows(j).Cells(4).Value = 0                    .Rows(j).Cells(5).Value = 0                Next                   .Rows.Add()            End With

还是没有实现一次sql实现，在研究还有什么好的方法，还有第一次读取的结果加在第二次后面的In 会提高效率，但数据量不是很大，并没有写

0 0