分组统计和交叉统计的实现原理

普通用户请忽略本节内容。

FoxTable的分组统计和交叉统计非常强大,几乎无所不能,看上去很神秘。
其实我们自己完全可以编码实现类似的功能。
本节的内容没有太多实际的意义,只是为了让大家更好的融合所学到的知识。

在学习本课的内容之前,请打开示例文件"统计演示.Table"。
本节内容的关键知识在于
DataTableBuilder类型和GetValues方法,如果你对他们已经生疏,请先温习一下。

示例一

统计不同产品的销售数量和金额:

Dim dtb As New DataTableBuilder("统计")
dtb.AddDef(
"产品", Gettype(String), 16)
dtb.AddDef(
"数量", Gettype(Integer))
dtb.AddDef(
"金额", Gettype(Double))
dtb.Build()

For Each
nm As String In DataTables("订单").GetValues("产品")
   
Dim dr As DataRow = DataTables("统计").AddNew()
    dr(
"产品") = nm
    dr(
"数量") = DataTables("订单").Compute("Sum(数量)","[产品] = '" & dr("产品") & "'")
    dr("金额") = DataTables("订单").Compute("Sum(金额)","[产品] = '" & dr("产品") & "'")

Next
MainTable
= Tables("统计")

示例二

统计每个客户订购不同产品的数量和金额:

Dim dtb As New DataTableBuilder("统计")
dtb.AddDef(
"客户", Gettype(String), 16)
dtb.AddDef(
"产品", Gettype(String), 16)
dtb.AddDef(
"数量", Gettype(Integer))
dtb.AddDef(
"金额", Gettype(Double))
dtb.Build()

For Each
nm() As String In DataTables("订单").GetValues("客户|产品")
    Dim
dr As DataRow = DataTables("统计").AddNew()
    dr(
"客户") = nm(0)
   
dr("产品") = nm(1)
    dr(
"数量") = DataTables("订单").Compute("Sum(数量)","客户 = '" & nm(0) & "' And [产品] = '" & nm(1) & "'")
    dr(
"金额") = DataTables("订单").Compute("Sum(金额)","客户 = '" & nm(0) & "' And [产品] = '" & nm(1) & "'")
Next
MainTable
= Tables("统计")

示例三

以交叉统计的形式,统计每个客户订购不同产品的数量:

Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("客户", Gettype(String), 16)

Dim
prds As List(of String) = DataTables("订单").GetValues("产品")
For Each
prd As String In prds
    dtb.AddDef(prd, Gettype(Double
))
Next

dtb.Build
()
For Each
cus As String In DataTables("订单").GetValues("客户")
    Dim
dr As DataRow = DataTables("统计").AddNew()
    dr("客户") = cus

    For
Each prd As String In prds
        dr(prd) = DataTables("订单").Compute("Sum(数量)","客户 = '" & cus & "' And [产品] = '" & prd & "'")

    Next
Next
MainTable= Tables(
"统计")

示例四

以交叉统计的形式,统计每个客户订购不同产品的数量和金额:

Dim dtb As New DataTableBuilder("统计")
dtb.AddDef(
"客户", Gettype(String), 16)
Dim
prds As List(of String) = DataTables("订单").GetValues("产品")
For Each
prd As String In prds
    dtb.AddDef(prd & "_数量", Gettype(Integer))
    dtb.AddDef(prd &
"_金额", Gettype(Double))
Next

dtb.Build()

For
Each cus As String In DataTables("订单").GetValues("客户")
    Dim
dr As DataRow = DataTables("统计"
).AddNew()
    dr(
"客户") = cus
    For
Each prd As String In
prds
        dr(prd &
"_数量") = DataTables("订单").Compute("Sum(数量)","客户 = '" & cus & "' And [产品] = '" & prd & "'")
        dr(prd &
"_金额") = DataTables("订单").Compute("Sum(金额)","客户 = '" & cus & "' And [产品] = '" & prd & "'")
    Next
Next
MainTable
= Tables("统计")


本页地址:http://www.foxtable.com/webhelp/topics/2394.htm