耗时 3百多 秒
Dim g As New SQLGroupTableBuilder("统计表1", "明细表")
g.Connection Name = "ceshi"
g.Groups.AddDef("日期", DateGroupEnum.Year, "年份")
g.Groups.AddDef("日期", "月份")
g.Groups.AddDef("产品id")
g.Totals.AddDef("单价", AggregateEnum.Average)
g.Totals.AddDef("数量","入库")
g.Totals.AddExp("出库","0.00")
g.Totals.AddExp("库存","0.00")
g.Filter = "year(日期) = '2021' and month(日期) in ('1','2','3')"
Dim dt As DataTable = g.Build(True)
dt.DataCols.Add("日期", Gettype(String), "[年份] + SubString('00', 1, 2 - Len(Convert([月份],'System.String'))) + Convert([月份], 'System.String')")
dt.ReplaceFor("出库", 1)
Dim Products As List(Of String) = dt.GetValues("产品id")
For Each Product As String In Products
Dim drs As List(of DataRow) = dt.Select("[产品id] = '" & Product & "'", "[日期]")
drs(0)("库存") = drs(0)("入库") - drs(0)("出库")
For i As Integer = 1 To drs.Count - 1
drs(i)("库存") = drs(i-1)("库存") + drs(i)("入库") - drs(i)("出库")
Next
Next
[此贴子已经被作者于2022/1/11 23:03:58编辑过]
加好友 
发短信
Post By：2022/1/11 19:24:00 [只看该作者]
