专家坐堂-跨表统计的运算速度Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

-- 跨表统计的运算速度

有点甜老师：你好！

帮助看看下面跨表统计的代码，3000多行的数据统计需花费20分钟，如何修改代码提高运算速度？

Dim dr As DataRow = e.DataRow

If e.DataCol.Name = "存货编码" Then

Dim QueryMonth As Date \'自定义日期

Dim QueryMonth1 As Date \'自定义日期

Dim CKQueryMonth As Date \'自定义日期

Dim CKJQueryMonth As Date \'自定义日期

CKJQueryMonth=Forms("进销存统计").Controls("结束日期").value

CKQueryMonth =Forms("进销存统计").Controls("起始日期").value

Dim StartDate As New Date(QueryMonth.Today.Year, 1, 1) \'当前日期的年度第一天

Dim EndDate As New Date(QueryMonth.Today.Year, 12 ,31) \'当前日期的年度最后一天

Dim ShangDate As New Date(QueryMonth.Today.Year-1) \'当前日期的上一年

Dim SDStartDate As New Date(QueryMonth.Today.Year-1, 1, 1) \'当前日期的上年度第一天

Dim SZEndDate As New Date(QueryMonth.Today.Year-1, 12 ,31) \'当前日期的上年度最后一天

Dim StartMonth As Date \'月份第一天

\'Dim khNum As String \'员工编号

\'khNum=e.DataRow("客户编码")

Dim Num As String \'员工编号

Num=e.DataRow("存货编码")

QueryMonth=e.DataRow("日期")

StartMonth=CStr(Year(QueryMonth)) & "-" & CStr(Month(QueryMonth)) & "-1"

Dim FirstDay As Date = New Date(QueryMonth.Year,QueryMonth.Month,1) \'当前月度第一天

Dim LastDay As Date = New Date(QueryMonth.Year,QueryMonth.Month,Date.DaysInMonth(QueryMonth.Year,QueryMonth.Month)) \'当前月度最后一天

\'Dim sFirstDay As Date = FirstDay.AddMonths(-1)\'上月第一天

\'Dim sLastDay As Date = New Date(QueryMonth.AddMonths(-1).Year,QueryMonth.AddMonths(-1).Month,Date.DaysInMonth(QueryMonth.AddMonths(-1).Year,QueryMonth.AddMonths(-1).Month)) \'上月最后一天

Dim sFirstDay As Date = New Date(QueryMonth.Year,QueryMonth.Month-1,1) \'上月第一天

Dim sLastDay As Date = New Date(QueryMonth.Year,QueryMonth.Month-1,Date.DaysInMonth(QueryMonth.Year,QueryMonth.Month-1)) \'上月最后一天

Dim rksl As Double

Dim qcsl As Double

Dim cksl As Double

Dim ckje As Double

Dim rkje As Double

Dim qcje As Double

Dim ckdj As Double

Dim thsl As Double

Dim thje As Double

Dim tlsl As Double

Dim tlje As Double

Dim kcsl As Double

Dim kcje As Double

Dim sgcksl As Double

Dim sgckje As Double

Dim rq =dr("日期")

\'提取当前日期前,期初库存中的期初数量

Dim cmd3 As New SQLCommand

cmd3.C

Dim dt3 As DataTable

If rq >= "2017-09-01 "

cmd3.CommandText = "SELECT sum([库存数量]) As 期初数量 F rom {进销存统计} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & sFirstDay & "\' And [日期] <= \'" & sLastDay & "\' "

Else

cmd3.CommandText = "SELECT sum([期初数量]) As 期初数量 F rom {期初库存} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & sFirstDay & "\' And [日期] <= \'" & sLastDay & "\' "

End If

dt3 = cmd3.ExecuteReader

If dt3.DataRows.Count > 0 Then

qcsl = Math.Round((dt3.DataRows(0)("期初数量")),2)

\'dr("期初数量") = qcsl

End If

\'提取当前日期前,期初库存中的期初金额

Dim cmd4 As New SQLCommand

cmd4.C

Dim dt4 As DataTable

If rq >= "2017-09-01 "

cmd4.CommandText = "SELECT sum([库存金额]) As 期初金额 F rom {进销存统计} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & sFirstDay & "\' And [日期] <= \'" & sLastDay & "\' "

Else

cmd4.CommandText = "SELECT sum([期初金额]) As 期初金额 F rom {期初库存} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & sFirstDay & "\' And [日期] <= \'" & sLastDay & "\' "

End If

dt4 = cmd4.ExecuteReader

If dt4.DataRows.Count > 0 Then

\'qcje = dt4.DataRows(0)("期初金额")

qcje = Math.Round((dt4.DataRows(0)("期初金额")),2)

\'dr("期初金额") = qcje

End If

\'提取当前日期前,出库明细中的入库数量

Dim cmd5 As New SQLCommand

cmd5.C

Dim dt5 As DataTable

cmd5.CommandText = "SELECT sum([入库数量]) As 入库数量 F rom {入库明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' and [客户编码] is null "

dt5 = cmd5.ExecuteReader

If dt5.DataRows.Count > 0 Then

rksl = Math.Round((dt5.DataRows(0)("入库数量")),2)

\'dr("入库数量") = rksl

End If

\'提取当前日期前,出库明细中的入库金额

Dim cmd6 As New SQLCommand

cmd6.C

Dim dt6 As DataTable

cmd6.CommandText = "SELECT sum([入库金额]) As 入库金额 F rom {入库明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' and [客户编码] is null "

dt6 = cmd6.ExecuteReader

If dt6.DataRows.Count > 0 Then

rkje = Math.Round((dt6.DataRows(0)("入库金额")),2)

\'dr("入库金额") = rkje

End If

\'提取当前日期前,退货明细中的入库数量

Dim cmd7 As New SQLCommand

cmd7.C

Dim dt7 As DataTable

cmd7.CommandText = "SELECT sum([退货数量]) As 退货数量 F rom {退货明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' "

dt7 = cmd7.ExecuteReader

If dt7.DataRows.Count > 0 Then

thsl = Math.Round((dt7.DataRows(0)("退货数量")),2)

\'dr("退货数量") = thsl

End If

\'提取当前日期前,退货明细中的入库金额

Dim cmd8 As New SQLCommand

cmd8.C

Dim dt8 As DataTable

cmd8.CommandText = "SELECT sum([金额]) As 金额 F rom {退货明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' "

dt8 = cmd8.ExecuteReader

If dt8.DataRows.Count > 0 Then

thje = Math.Round((dt8.DataRows(0)("金额")),2)

\'dr("退货金额") = thje

End If

\'提取当前日期前,出库明细中的入库数量

Dim cmd1 As New SQLCommand

cmd1.C

Dim dt1 As DataTable

cmd1.CommandText = "SELECT sum([出库数量]) As 出库数量 F rom {申购明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' and [AB分类] = \'A\' "

dt1 = cmd1.ExecuteReader

If dt1.DataRows.Count > 0 Then

cksl = Math.Round((dt1.DataRows(0)("出库数量")),2)

\'dr("出库数量") = cksl

\'rksl = cksl

\'dr("入库数量") = rksl

End If

\'提取当前日期前,出库明细中的入库金额

Dim cmd2 As New SQLCommand

cmd2.C

Dim dt2 As DataTable

cmd2.CommandText = "SELECT sum([进货金额]) As 进货金额 F rom {申购明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' and [AB分类] = \'A\' "

dt2 = cmd2.ExecuteReader

If dt2.DataRows.Count > 0 Then

ckje = Math.Round((dt2.DataRows(0)("进货金额")),2)

\'dr("出库金额") = ckje

\'rkje = ckje

\'dr("入库金额") = rkje

End If

\'提取当前日期前,退料明细中的入库数量

Dim cmd9 As New SQLCommand

cmd9.C

Dim dt9 As DataTable

cmd9.CommandText = "SELECT sum([退料数量]) As 退料数量 F rom {退料明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' "

dt9 = cmd9.ExecuteReader

If dt9.DataRows.Count > 0 Then

tlsl = Math.Round((dt9.DataRows(0)("退料数量")),2)

\'dr("退料数量") = tlsl

End If

\'提取当前日期前,退料明细中的入库金额

Dim cmd10 As New SQLCommand

cmd10.C

Dim dt10 As DataTable

cmd10.CommandText = "SELECT sum([金额]) As 金额 F rom {退料明细} Where [存货编码] = \'" & num & "\' And [日期] >= \'" & FirstDay & "\' And [日期] <= \'" & LastDay & "\' "

dt10 = cmd10.ExecuteReader

If dt10.DataRows.Count > 0 Then

tlje = Math.Round((dt10.DataRows(0)("金额")),2)

\'dr("退料金额") = tlje

End If

dr("期初金额") = qcje

dr("期初数量") = qcsl

If qcsl <> 0 Then

dr("期初单价") = qcje / qcsl

Else

dr("期初单价") = 0

End If

If rksl-thsl <> 0 Then

dr("入库数量") = rksl - thsl

dr("入库金额") = rkje - thje

dr("入库单价") = (rkje - thje) / (rksl - thsl)

Else

dr("入库单价") = 0

End If

If cksl - tlsl <> 0 Then

dr("出库数量") = cksl - tlsl

dr("出库金额") = ckje - tlje

dr("出库单价") = (ckje - tlje) / (cksl - tlsl)

Else

dr("出库单价") = 0

End If

Select Case e.DataCol.Name

Case "存货编码"

dr("库存数量") = dr("期初数量") + dr("入库数量") - dr("出库数量")

dr("库存金额") = dr("期初金额") + dr("入库金额") - dr("出库金额")

dr("库存单价") = Math.Abs(dr("库存金额") / dr("库存数量"))

End Select

Select Case e.DataCol.Name

Case "盘点数量"

If dr.IsNull("盘点数量") Then

dr("盘点金额") = Nothing

Else

dr("库存数量") = dr("盘点数量")

dr("出库数量") = dr("入库数量") - dr("库存数量")

dr("出库金额") = Math.Round(dr("出库数量") * dr("出库单价"),2)

dr("库存单价") = Math.Round((dr("期初金额") + dr("入库金额") ) / (dr("期初数量") + dr("入库数量") ),4)

dr("库存金额") = dr("库存单价") * dr("库存数量")

dr("盘点金额") = dr("库存金额")

End If

End Select