前一阵子在做一个关于配件维修程序的项目
技术选型为vb.Net 和access数据库
数据库的结构是这样的
分别有客户表,机器表,配件表三个基本信息表
将客户和机器信息对应形成客户机器表,为客户机器表中的机器添加配件形成机器配件表,像机器配件表里加维修时间形成配件维修表
在一个datagridview中我首先想实现的功能是统计客户的机器数
sql 语句是这么写的
还是没有实现一次sql实现,在研究还有什么好的方法,还有第一次读取的结果加在第二次后面的In 会提高效率,但数据量不是很大,并没有写
技术选型为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 Function
ErrorHandler:
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 Function
ErrorHandler:
'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 会提高效率,但数据量不是很大,并没有写