- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助]GroupTableBuilder 分组统计问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=185982)
-- 发布时间：2023/3/29 19:23:00
-- [求助]GroupTableBuilder 分组统计问题
老师，如何才可以得到后面的分级汇总
Dim tb As Table = Tables(e.Form.Name & "_table1")
Dim dt As DataTable = tb.DataTable
Dim fi As String
If e.Form.Controls("VTextfi").text > "" Then
Fi = "sel ect * from {YGB} where " & e.Form.Controls("VTextfi").text
Else
Fi = "sele ct * from {YGB} "
End If
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = fi
tb.DataSource = cmd.ExecuteReader
Dim b As New GroupTableBuilder("DDCJNl", dt)
\'b.C
b.Groups.AddDef("在职情况")
b.Totals.AddDef("年龄", AggregateEnum.Max, "最高龄")
b.Totals.AddDef("年龄", AggregateEnum.Min, "最低龄")
b.Totals.AddDef("年龄", AggregateEnum.Average, "平均年龄")
b.Totals.Addexp("分布_20以下", "iif(年龄 < 20 ,1,0)")
b.Totals.AddExp("分布_20-25", "iif(年龄 >= 20 And 年龄 < 25,1,0)")
b.Totals.AddExp("分布_25-30", "iif(年龄 >= 25 And 年龄 < 30,1,0)")
b.Totals.AddExp("分布_30-35", "iif(年龄 >= 30 And 年龄 < 35,1,0)")
b.Totals.AddExp("分布_35-40", "iif(年龄 >= 35 And 年龄 < 40,1,0)")
b.Totals.AddExp("分布_40-45", "iif(年龄 >= 40 And 年龄 < 45,1,0)")
b.Totals.AddExp("分布_45-50", "iif(年龄 >= 45 And 年龄 < 50,1,0)")
b.Totals.AddExp("分布_50-60", "iif(年龄 >= 50 And 年龄 < 60,1,0)")
b.Totals.AddExp("分布_60以上", "iif(年龄 >= 60,1,0)")
此主题相关图片如下：无标题20230329.png
[此贴子已经被作者于2023/3/29 19:24:45编辑过]
-- 发布时间：2023/3/30 8:45:00
--
GroupTableBuilder改为使用SqlGroupTableBuilder
-- 发布时间：2023/3/30 15:16:00
--
Dim tb As Table = Tables(e.Form.Name & "_table1")
Dim dt As DataTable = tb.DataTable
Dim fi As String
If e.Form.Controls("VTextfi").text > "" Then
Fi = "se lect * from {" & e.Form.Controls("Txt数据视图").text & "} where " & e.Form.Controls("VTextfi").text
Else
Fi = "selec t * from {" & e.Form.Controls("Txt数据视图").text & "} "
End If
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = fi
tb.DataSource = cmd.ExecuteReader
Dim b As New sqlGroupTableBuilder("DDCJNl", dt.Name)
\'b.C
b.Groups.AddDef("在职情况")
b.Totals.AddDef("年龄", AggregateEnum.Max, "最高龄")
b.Totals.AddDef("年龄", AggregateEnum.Min, "最低龄")
b.Totals.AddDef("年龄", AggregateEnum.Average, "平均年龄")
b.Totals.Addexp("分布_20以下", "iif(年龄 < 20 ,1,0)")
b.Totals.AddExp("分布_20-25", "iif(年龄 >= 20 And 年龄 < 25,1,0)")
b.Totals.AddExp("分布_25-30", "iif(年龄 >= 25 And 年龄 < 30,1,0)")
b.Totals.AddExp("分布_30-35", "iif(年龄 >= 30 And 年龄 < 35,1,0)")
b.Totals.AddExp("分布_35-40", "iif(年龄 >= 35 And 年龄 < 40,1,0)")
b.Totals.AddExp("分布_40-45", "iif(年龄 >= 40 And 年龄 < 45,1,0)")
b.Totals.AddExp("分布_45-50", "iif(年龄 >= 45 And 年龄 < 50,1,0)")
b.Totals.AddExp("分布_50-60", "iif(年龄 >= 50 And 年龄 < 60,1,0)")
b.Totals.AddExp("分布_60以上", "iif(年龄 >= 60,1,0)")
Dim Tabcj As Table = Tables(e.Form.Name & "_TabCj")
Dim DtCj As DataTable = tabcj.DataTable
tabcj.DataSource = b.BuildDataSource
错误提示，生成SQL语句错误，无法统计
-- 发布时间：2023/3/30 15:35:00
--
这里前面的代码全部去掉
Dim b As New sqlGroupTableBuilder("DDCJNl", e.Form.Controls("Txt数据视图").text)b.Filter = e.Form.Controls("VTextfi").text
b.ConnectionName=“xx数据源”
b.Groups.AddDef("在职情况")
……
-- 发布时间：2023/3/30 17:00:00
--
Dim b As New sqlGroupTableBuilder("DDCJNl", e.Form.Controls("Txt数据视图").text)
b.C
b.Filter = e.Form.Controls("VTextfi").text
b.Groups.AddDef("在职情况")
b.Totals.AddDef("年龄", AggregateEnum.Max, "最高龄")
b.Totals.AddDef("年龄", AggregateEnum.Min, "最低龄")
b.Totals.AddDef("年龄", AggregateEnum.Average, "平均年龄")
b.Totals.Addexp("分布_20以下", "iif(年龄 < 20 ,1,0)")
b.Totals.AddExp("分布_20-25", "iif(年龄 >= 20 And 年龄 < 25,1,0)")
b.Totals.AddExp("分布_25-30", "iif(年龄 >= 25 And 年龄 < 30,1,0)")
b.Totals.AddExp("分布_30-35", "iif(年龄 >= 30 And 年龄 < 35,1,0)")
b.Totals.AddExp("分布_35-40", "iif(年龄 >= 35 And 年龄 < 40,1,0)")
b.Totals.AddExp("分布_40-45", "iif(年龄 >= 40 And 年龄 < 45,1,0)")
b.Totals.AddExp("分布_45-50", "iif(年龄 >= 45 And 年龄 < 50,1,0)")
b.Totals.AddExp("分布_50-60", "iif(年龄 >= 50 And 年龄 < 60,1,0)")
b.Totals.AddExp("分布_60以上", "iif(年龄 >= 60,1,0)")
Tables(e.Form.Name & "_TabCj").DataSource = b.BuildDataSource
老师，还是报以下错误
此主题相关图片如下：无标题20230330.png
[此贴子已经被作者于2023/3/30 17:01:04编辑过]
-- 发布时间：2023/3/30 17:05:00
--
什么数据库？
-- 发布时间：2023/3/30 17:31:00
--
MSSQL 已解决
Dim b As New sqlGroupTableBuilder("DDCJNl", e.Form.Controls("Txt数据视图").text)
b.C
b.Filter = e.Form.Controls("VTextfi").text
b.Groups.AddDef("在职情况")
b.Totals.AddDef("年龄", AggregateEnum.Max, "最高龄")
b.Totals.AddDef("年龄", AggregateEnum.Min, "最低龄")
b.Totals.AddDef("年龄", AggregateEnum.Average, "平均年龄")
b.Totals.Addexp("分布_20以下", "case when 年龄 < 20 then 1 else 0 end")
b.Totals.AddExp("分布_20-25", "case when 年龄 >= 20 And 年龄 < 25 then 1 else 0 end")
b.Totals.AddExp("分布_25-30", "case when 年龄 >= 25 And 年龄 < 30 then 1 else 0 end")
b.Totals.AddExp("分布_30-35", "case when 年龄 >= 30 And 年龄 < 35 then 1 else 0 end")
b.Totals.AddExp("分布_35-40", "case when 年龄 >= 35 And 年龄 < 40 then 1 else 0 end")
b.Totals.AddExp("分布_40-45", "case when 年龄 >= 40 And 年龄 < 45 then 1 else 0 end")
b.Totals.AddExp("分布_45-50", "case when 年龄 >= 45 And 年龄 < 50 then 1 else 0 end")
b.Totals.AddExp("分布_50-60", "case when 年龄 >= 50 And 年龄 < 60 then 1 else 0 end")
b.Totals.AddExp("分布_60以上", "case when 年龄 >= 60 then 1 else 0 end")
Tables(e.Form.Name & "_TabCj").DataSource = b.BuildDataSource