[求助]查询与赋值并存 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim Dic1，Dic2，Dic2 As new Dictionary(of DataRow, Integer)
Dim v1，v2，v3 As Double
For Each dr As DataRow In DataTables("表A").DataRows
v1 = DataTables("表A").Compute("Count(第二列 )", "第二列 = " & dr("第一列"))

v2 = DataTables("表A").Compute("Count(第三列 )", "第三列 = " & dr("第一列"))

v3 = DataTables("表A").Compute("Count(第四列 )", "第四列 = " & dr("第一列"))

dic1.Add(dr, v1)

dic2.Add(dr, v2)

dic3.Add(dr, v3)
Next
For Each dr As DataRow In dic1.Keys
dr("第五列") = dic1(dr)
Next

For Each dr As DataRow In dic2.Keys
dr("第六列") = dic2(dr)
Next

For Each dr As DataRow In dic3.Keys
dr("第七列") = dic3(dr)
Next

上述代码还可以整得简洁点吗

[此贴子已经被作者于2017/8/25 9:19:19编辑过]

上传实例，直接说明你要实现的功能。

Dim Dic1,Dic2,Dic3 As new Dictionary(of DataRow, Integer)

Dim v1,v2,v3 As Double

For Each dr As DataRow In DataTables("日报表").DataRows

v1 = DataTables("销售日报").SQLCompute("sum(销售数量)", "[日期] = '" & dr("日期") & "' and [店铺名称] = '" & dr("店铺名称") & "'")

v2 = DataTables("销售日报").SQLCompute("sum(销售金额)", "[日期] = '" & dr("日期") & "' and [店铺名称] = '" & dr("店铺名称") & "'")

v3 = DataTables("销售日报").SQLCompute("sum(销售成本)", "[日期] = '" & dr("日期") & "' and [店铺名称] = '" & dr("店铺名称") & "'")

dic1.Add(dr, v1)

dic2.Add(dr, v2)

dic3.Add(dr, v3)

For Each dr As DataRow In dic1.Keys

dr("销售数量") = dic1(dr)

For Each dr As DataRow In dic2.Keys

dr("销售金额") = dic2(dr)

For Each dr As DataRow In dic3.Keys

dr("销售成本") = dic3(dr)

哦，上面代码能实现我需要的功能，只是我要赋值的例比较多的时候，这代码有点长，看能不能弄得简洁一点

[此贴子已经被作者于2017/8/25 9:29:47编辑过]

1、不要频繁使用sqlCompute，这样效率低下；还不如先把要统计的数据加载出来或先统计出来，再查找。

2、

Dim ary() As String = {"销售数量", "销售金额", "销售成本"}
Dim dic As new Dictionary(Of DataRow, List(of double))
For Each dr As DataRow In DataTables("阿米巴基础日报表").DataRows
    Dim filter As String = "[日期] = '" & dr("日期") & "' and [店铺名称] = '" & dr("店铺名称") & "'"
    Dim ls As new List(Of Double)
    For Each s As String In ary
        Dim sum As Double = DataTables("销售日报").SQLCompute("sum(" & s & ")", filter)
        ls.add(sum)
    Next
    dic.add(dr, ls)
Next
For Each dr As DataRow In dic.Keys
    For i As Integer = 0 To ary.length-1
        dr(ary(i)) = dic(dr)(i)
    Next
Next

确实，用sqlCompute速度很慢，我再偿试下先加载再计算

主题：[求助]查询与赋值并存