首先不建议使用这样的表结构，月报和年报应该单独出来一个表，可以有效减少大量的冗余数据和处理时间。
下面是使用代码的处理，结合统计表和SQL更新，本机数据库测试在5秒左右
Dim st As Date = Date.Now
Dim g As New GroupTableBuilder("统计表1", DataTables("日报"))
g.FromServer = True
g.Groups.AddDef("市场部")
g.Groups.AddDef("报表日期", DateGroupEnum.Year, "年")
g.Groups.AddDef("报表日期", "月")
g.Totals.AddDef("本期沙日销")
g.Totals.AddDef("本期石日销")
'g.Build
Dim dt As DataTable = g.Build(True)
Dim cmd As new SQLCommand
cmd.C
cmd.CommandText = "UPDATE 日报 SET 本期沙年累计 = ? ,本期石年累计=?, 本期沙月累计=?, 本期石月累计=? WHERE 市场部= ? and year(报表日期)=? and MONTH(报表日期)=?"
For Each s As String() In dt.GetValues("市场部|年|月")
For Each dr As DataRow In dt.Select("市场部='" & s(0) & "' and 年=" & s(1) & " and 月=" & s(2))
Dim cnt1 As Double = dt.Compute("sum(本期石日销)","市场部='" & s(0) & "' and 年=" & s(1) & "and 月 = " & s(2))
Dim cnt2 As Double = dt.Compute("sum(本期沙日销)","市场部='" & s(0) & "' and 年=" & s(1) & "and 月 = " & s(2))
Dim cnt12 As Double = dt.Compute("sum(本期石日销)","市场部='" & s(0) & "' and 年=" & s(1) & "and 月 <= " & s(2))
Dim cnt22 As Double = dt.Compute("sum(本期沙日销)","市场部='" & s(0) & "' and 年=" & s(1) & "and 月 <= " & s(2))
cmd.Parameters.Clear
cmd.Parameters.Add("@本期沙月累计",cnt2 )
cmd.Parameters.Add("@本期石月累计",cnt1)
cmd.Parameters.Add("@本期沙年累计",cnt22)
cmd.Parameters.Add("@本期石年累计",cnt12)
cmd.Parameters.Add("@市场部",s(0))
cmd.Parameters.Add("@年",s(1))
cmd.Parameters.Add("@月",s(2))
cmd.ExecuteNonQuery
Next
Next
MessageBox.Show("耗时: " & (Date.Now - st).TotalSeconds & "秒") '计算并显示执行代码所花费的秒数
下面是纯sql的方式，分2条SQL，如果给表加上合适的索引，执行大概在1秒内
update [日报] set 本期石月累计 = a.本期石月累计,本期沙月累计=a.本期沙月累计
from (
select [市场部],year([报表日期]) as 年,MONTH([报表日期]) as 月, sum([本期石日销]) as 本期石月累计,sum([本期沙日销]) as 本期沙月累计
from [日报]
group by [市场部],year([报表日期]),MONTH([报表日期])
) as a
where a.[市场部] = [日报].[市场部] and a.[年] = year([日报].[报表日期]) and a.[月] = MONTH([日报].[报表日期] )
update [日报] set 本期石年累计 = m.本期石年累计 ,本期沙年累计 = m.本期沙年累计
from (
select b.[市场部],b.年,b.月,sum(a.本期沙月累计) as 本期沙年累计, sum(a.本期石月累计) as 本期石年累计
from (
select [市场部],year([报表日期]) as 年,MONTH([报表日期]) as 月, sum([本期石日销]) as 本期石月累计,sum([本期沙日销]) as 本期沙月累计
from [日报] group by [市场部],year([报表日期]),MONTH([报表日期])
) as a
inner join (
select [市场部],year([报表日期]) as 年,MONTH([报表日期]) as 月, sum([本期石日销]) as 本期石月累计,sum([本期沙日销]) as 本期沙月累计
from [日报] group by [市场部],year([报表日期]),MONTH([报表日期])
) as b
on a.市场部 = b.市场部 and a.年 = b.年 and a.月 <= b.月
group by b.[市场部],b.年,b.月
) as m where m.[市场部] = [日报].[市场部] and m.[年] = year([日报].[报表日期]) and m.[月] = MONTH([日报].[报表日期] )
加好友 
发短信
Post By：2019/8/28 23:19:00 [只看该作者]
