主题：[求助]多表交叉统计 增加统计历史保存

代码一样。

1、如果勾选，那么就执行平均数的统计代码；

2、去表格查找对应的行，然后给左边逐个赋值进去；

3、

If  e.Form.Controls("CheckedComboBox5").Text <> "" Then
    Dim rstr As String
    For Each tname As String In e.Form.Controls("CheckedComboBox5").Text.split("|")
        For Each c As Col In Tables(tname).Cols
            rstr = rstr & c.Name & "|"
        Next     
    Next
   
    rstr =rstr.Trim("|")
    Dim cmb As WinForm.CheckedComboBox = e.Form.Controls("CheckedComboBox8")
    cmb.ComboList = rstr
End If

 我测试，没问题。

If  e.Form.Controls("CheckedComboBox5").Text <> "" Then
    Dim rstr As String
    For Each tname As String In e.Form.Controls("CheckedComboBox5").Text.split("|")
        For Each c As Col In Tables(tname).Cols
            If rstr.Contains(c.name) = False Then
                rstr = rstr & c.Name & "|"
            End If
        Next
    Next
   
    rstr = rstr.Trim("|")
    Dim cmb As WinForm.CheckedComboBox = e.Form.Controls("CheckedComboBox8")
    cmb.ComboList = rstr
End If

Dim sql As String = ""
Dim ls_cols As New List(of String)
For Each tname As String In e.Form.Controls("CheckedComboBox5").Text.split("|")
    For Each c As Col In Tables(tname).Cols
        If ls_cols.Contains(c.name) = False Then
            ls_cols.add(c.Name)
        End If
    Next
Next

For Each s As String In e.Form.Controls("CheckedComboBox5").text.Split("|")
    Dim tmp As String = ""
    For Each cname As String In ls_Cols
        If Tables(s).Cols.Contains(cname) Then
            tmp &= "[" & cname & "] As [" & cname & "],"
        Else
            tmp &= "0 as [" & cname & "],"
        End If
    Next
    sql &= " select " & tmp.trim(",") & " from {" & s & "} union all"
Next
sql = "select * from {分析数据} a inner join (" & sql.substring(0, sql.Length-9) & ") As b on a.公历年月日 = b.时间"
output.show(sql)
Dim b As New crossTableBuilder("统计表1", sql)
For Each s As String In e.Form.Controls("CheckedComboBox6").text.Split("|")
    b.HGroups.AddDef(s) '添加列用于垂直分组
Next
For Each s As String In e.Form.Controls("CheckedComboBox7").text.Split("|")
    b.VGroups.AddDef(s) '添加列用于水平分组
Next

For Each s As String In e.Form.Controls("CheckedComboBox8").text.Split("|")
    If e.Form.Controls("CheckBox5").checked = True Then   '平均数统计
        b.Totals.AddDef(s,AggregateEnum.Average)
    Else
        b.Totals.AddDef(s)  '累计统计
    End If
Next

b.Decimals = 2

If e.Form.Controls("CheckBox1").checked = True Then   '水平方向生成汇总
    b.HorizontalTotal = True
End If
If e.Form.Controls("CheckBox2").checked = True Then   '垂直方向生成汇总
    b.VerticalTotal = True
End If
If e.Form.Controls("CheckBox3").checked = True Then   '自动生成汇总模式
    b.Subtotal = True
End If

b.Filter = e.Form.Controls("TextBox1").Text

b.Build '生成统计表
MainTable = Tables("统计表1") '打开生成的统计表
'Tables("统计_Table1").DataSource= b.BuildDataSource   '统计表放入指定窗口表

Dim sql As String = ""
Dim ls_cols As New List(of String)
For Each tname As String In e.Form.Controls("CheckedComboBox5").Text.split("|")
    For Each c As Col In Tables(tname).Cols
        If ls_cols.Contains(c.name) = False Then
            ls_cols.add(c.Name)
        End If
    Next
Next

For Each s As String In e.Form.Controls("CheckedComboBox5").text.Split("|")
    Dim tmp As String = ""
    For Each cname As String In ls_Cols
        If Tables(s).Cols.Contains(cname) Then
            tmp &= "[" & cname & "] As [" & cname & "],"
        Else
            tmp &= "0.0 as [" & cname & "],"
        End If
    Next
    sql &= " select " & tmp.trim(",") & " from {" & s & "} union all"
Next
sql = "select * from {分析数据} a inner join (" & sql.substring(0, sql.Length-9) & ") As b on a.公历年月日 = b.时间"
output.show(sql)
Dim b As New crossTableBuilder("统计表1", sql)
For Each s As String In e.Form.Controls("CheckedComboBox6").text.Split("|")
    b.HGroups.AddDef(s) '添加列用于垂直分组
Next
For Each s As String In e.Form.Controls("CheckedComboBox7").text.Split("|")
    b.VGroups.AddDef(s) '添加列用于水平分组
Next

For Each s As String In e.Form.Controls("CheckedComboBox8").text.Split("|")
    If e.Form.Controls("CheckBox5").checked = True Then   '平均数统计
        b.Totals.AddDef(s,AggregateEnum.Average)
    Else
        b.Totals.AddDef(s)  '累计统计
    End If
Next

b.Decimals = 2

If e.Form.Controls("CheckBox1").checked = True Then   '水平方向生成汇总
    b.HorizontalTotal = True
End If
If e.Form.Controls("CheckBox2").checked = True Then   '垂直方向生成汇总
    b.VerticalTotal = True
End If
If e.Form.Controls("CheckBox3").checked = True Then   '自动生成汇总模式
    b.Subtotal = True
End If

b.Filter = e.Form.Controls("TextBox1").Text

b.Build '生成统计表
If e.Form.Controls("CheckBox3").checked = True Then   '自动生成汇总模式
    For Each c As Col In Tables("统计表1").Cols
        If c.IsNumeric Then
            c.DataCol.SetFormat("0.000")
        End If
    Next
End If
MainTable = Tables("统计表1") '打开生成的统计表
'Tables("统计_Table1").DataSource= b.BuildDataSource   '统计表放入指定窗口表