以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  如何提高统计效率  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=184422)
--  作者：刘林
--  发布时间：2022/12/6 20:23:00
--  如何提高统计效率

请问老师，我用很土的办法实现了上面的统计，但有个问题是前面的sqlGroupTableBuilder部分很快，闪出结果，后面的校平和区平用addnew再循环统计平均的办法较慢，请问要实现同样效果有什么好的办法来提高统计效率


图片点击可在新窗口打开查看此主题相关图片如下：qq图片20221206202012.png
图片点击可在新窗口打开查看
 下载信息  [文件大小：   下载次数： ]
图片点击可在新窗口打开查看点击浏览该文件:test.txt
--  作者：有点蓝
--  发布时间：2022/12/6 20:52:00
--  
下面这种统计可以先按年级、考试名称、学段另外进行一个姓名计数的分组统计，然后对统计表进行find查询获取统计值即可

   Dim qqnjrs As Integer = DataTables("成绩").SQLCompute("count(姓名)","年级= \'" & njpj & "\'  and 考试名称 = \'" & ccb1 & "\'and 学段=\'初中\' And (免统审核 = 0 Or 免统审核 Is null) ")
--  作者：刘林
--  发布时间：2022/12/6 21:12:00
--  

我还有个思路是当成绩记录确定后，统计表就是确定，可否导出EXCEL后存在在服务器上，当打开地判断是否存在指写的EXCEL，如果存在就导入到临时表中进行相关操作即可，这样也不用每次都去计算等待，但现有有个问题是导出的EXCE是多层表头，如果将多层表头的EXCE对应导入临时表呢？
--  作者：刘林
--  发布时间：2022/12/6 21:27:00
--  

Dim g As New sqlGroupTableBuilder("表1", "成绩")
g.C
g.filter ="学段 =\'初中\' and (免统审核 = 0 or 免统审核 is null) and 考试名称 =\'" & ccb1 & "\'"
g.Groups.AddDef("考试名称")
g.Groups.AddDef("单位名称")
g.Groups.AddDef("年级")
g.Groups.AddDef("学校班级")
g.Groups.AddDef("班级全称")
g.Totals.AddDef("姓名",AggregateEnum.Count,"参考人数")
g.Totals.AddDef("语文_A",AggregateEnum.Average,"语文_A卷")
g.Totals.AddDef("语文_B",AggregateEnum.Average,"语文_B卷")
g.Totals.AddDef("语文_B+ 语文_A*0.7",AggregateEnum.Average,"语文_折合")
g.Totals.AddDef("数学_A",AggregateEnum.Average,"数学_A卷")
g.Totals.AddDef("数学_B",AggregateEnum.Average,"数学_B卷")
g.Totals.AddDef("数学_B+ 数学_A*0.7",AggregateEnum.Average,"数学_折合")
g.Totals.AddDef("英语_A",AggregateEnum.Average,"英语_A卷")
g.Totals.AddDef("英语_B",AggregateEnum.Average,"英语_B卷")
g.Totals.AddDef("英语_B+ 英语_A*0.7",AggregateEnum.Average,"英语_折合")
g.Totals.AddDef("政治_A",AggregateEnum.Average,"政治_A卷")
g.Totals.AddDef("政治_B",AggregateEnum.Average,"政治_B卷")
g.Totals.AddDef("政治_B+ 政治_A*0.3",AggregateEnum.Average,"政治_折合")
g.Totals.AddDef("历史_A",AggregateEnum.Average,"历史_A卷")
g.Totals.AddDef("历史_B",AggregateEnum.Average,"历史_B卷")
g.Totals.AddDef("历史_B+ 历史_A*0.3",AggregateEnum.Average,"历史_折合")
g.Totals.AddDef("物理_A",AggregateEnum.Average,"物理_A卷")
g.Totals.AddDef("物理_B",AggregateEnum.Average,"物理_B卷")
g.Totals.AddDef("物理_B+ 物理_A*0.35+iif(物理实验 is null,0,物理实验)",AggregateEnum.Average,"物理_折合")
g.Totals.AddDef("化学_A",AggregateEnum.Average,"化学_A卷")
g.Totals.AddDef("化学_B",AggregateEnum.Average,"化学_B卷")
g.Totals.AddDef("化学_B+ 化学_A*0.3+iif(化学实验 Is null,0,化学实验)",AggregateEnum.Average,"化学_折合")
g.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_折合")
g.Totals.AddDef("物理实验",AggregateEnum.Average,"物理_实验")
g.Totals.AddDef("化学实验",AggregateEnum.Average,"化学_实验")
g.Totals.AddDef("生物_",AggregateEnum.Average,"生物_A卷")
g.Totals.AddDef("地理_",AggregateEnum.Average,"地理_A卷")
g.Totals.AddDef("生物_A*0.35",AggregateEnum.Average,"生物_折合")
g.Totals.AddDef("地理_A*0.35",AggregateEnum.Average,"地理_折合")
G.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_B卷")
g.Totals.AddDef("折总",AggregateEnum.Average,"折总平均")
g.Totals.Addexp("折总分段_500以上", "Case When 折总 >= 500 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_460至499", "Case When 折总 >= 460 and 折总<500 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_400至459", "Case When 折总 >= 400 and 折总< 460 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_350至399", "Case When 折总 >= 350 and 折总< 400 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_350以下", "Case When 折总< 350 Then 1 Else 0 End")
g.FromServer = True
tb3.DataSource = g.BuildDataSource()

老师，二楼的方法，我理解为将红色部分去掉，再生成一个统计，生成表2，将表2并入表1，按理就实现了，这个又如何实合并入表1呢
--  作者：刘林
--  发布时间：2022/12/6 21:33:00
--  

Dim g As New sqlGroupTableBuilder("表1", "成绩")
g.C
g.filter ="学段 =\'初中\' and (免统审核 = 0 or 免统审核 is null) and 考试名称 =\'" & ccb1 & "\'"
g.Groups.AddDef("考试名称")
g.Groups.AddDef("单位名称")
g.Groups.AddDef("年级")
g.Groups.AddDef("学校班级")
g.Groups.AddDef("班级全称")
g.Totals.AddDef("姓名",AggregateEnum.Count,"参考人数")
g.Totals.AddDef("语文_A",AggregateEnum.Average,"语文_A卷")
g.Totals.AddDef("语文_B",AggregateEnum.Average,"语文_B卷")
g.Totals.AddDef("语文_B+ 语文_A*0.7",AggregateEnum.Average,"语文_折合")
g.Totals.AddDef("数学_A",AggregateEnum.Average,"数学_A卷")
g.Totals.AddDef("数学_B",AggregateEnum.Average,"数学_B卷")
g.Totals.AddDef("数学_B+ 数学_A*0.7",AggregateEnum.Average,"数学_折合")
g.Totals.AddDef("英语_A",AggregateEnum.Average,"英语_A卷")
g.Totals.AddDef("英语_B",AggregateEnum.Average,"英语_B卷")
g.Totals.AddDef("英语_B+ 英语_A*0.7",AggregateEnum.Average,"英语_折合")
g.Totals.AddDef("政治_A",AggregateEnum.Average,"政治_A卷")
g.Totals.AddDef("政治_B",AggregateEnum.Average,"政治_B卷")
g.Totals.AddDef("政治_B+ 政治_A*0.3",AggregateEnum.Average,"政治_折合")
g.Totals.AddDef("历史_A",AggregateEnum.Average,"历史_A卷")
g.Totals.AddDef("历史_B",AggregateEnum.Average,"历史_B卷")
g.Totals.AddDef("历史_B+ 历史_A*0.3",AggregateEnum.Average,"历史_折合")
g.Totals.AddDef("物理_A",AggregateEnum.Average,"物理_A卷")
g.Totals.AddDef("物理_B",AggregateEnum.Average,"物理_B卷")
g.Totals.AddDef("物理_B+ 物理_A*0.35+iif(物理实验 is null,0,物理实验)",AggregateEnum.Average,"物理_折合")
g.Totals.AddDef("化学_A",AggregateEnum.Average,"化学_A卷")
g.Totals.AddDef("化学_B",AggregateEnum.Average,"化学_B卷")
g.Totals.AddDef("化学_B+ 化学_A*0.3+iif(化学实验 Is null,0,化学实验)",AggregateEnum.Average,"化学_折合")
g.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_折合")
g.Totals.AddDef("物理实验",AggregateEnum.Average,"物理_实验")
g.Totals.AddDef("化学实验",AggregateEnum.Average,"化学_实验")
g.Totals.AddDef("生物_",AggregateEnum.Average,"生物_A卷")
g.Totals.AddDef("地理_",AggregateEnum.Average,"地理_A卷")
g.Totals.AddDef("生物_A*0.35",AggregateEnum.Average,"生物_折合")
g.Totals.AddDef("地理_A*0.35",AggregateEnum.Average,"地理_折合")
G.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_B卷")
g.Totals.AddDef("折总",AggregateEnum.Average,"折总平均")
g.Totals.Addexp("折总分段_500以上", "Case When 折总 >= 500 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_460至499", "Case When 折总 >= 460 and 折总<500 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_400至459", "Case When 折总 >= 400 and 折总< 460 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_350至399", "Case When 折总 >= 350 and 折总< 400 Then 1 Else 0 End")
g.Totals.Addexp("折总分段_350以下", "Case When 折总< 350 Then 1 Else 0 End")
g.FromServer = True


Dim h As New sqlGroupTableBuilder("表2", "成绩")
h.C
h.filter ="学段 =\'初中\' and (免统审核 = 0 or 免统审核 is null) and 考试名称 =\'" & ccb1 & "\'"
h.Groups.AddDef("考试名称")
h.Groups.AddDef("单位名称")
h.Groups.AddDef("年级")
h.Totals.AddDef("姓名",AggregateEnum.Count,"参考人数")
h.Totals.AddDef("语文_A",AggregateEnum.Average,"语文_A卷")
h.Totals.AddDef("语文_B",AggregateEnum.Average,"语文_B卷")
h.Totals.AddDef("语文_B+ 语文_A*0.7",AggregateEnum.Average,"语文_折合")
h.Totals.AddDef("数学_A",AggregateEnum.Average,"数学_A卷")
h.Totals.AddDef("数学_B",AggregateEnum.Average,"数学_B卷")
h.Totals.AddDef("数学_B+ 数学_A*0.7",AggregateEnum.Average,"数学_折合")
h.Totals.AddDef("英语_A",AggregateEnum.Average,"英语_A卷")
h.Totals.AddDef("英语_B",AggregateEnum.Average,"英语_B卷")
h.Totals.AddDef("英语_B+ 英语_A*0.7",AggregateEnum.Average,"英语_折合")
h.Totals.AddDef("政治_A",AggregateEnum.Average,"政治_A卷")
h.Totals.AddDef("政治_B",AggregateEnum.Average,"政治_B卷")
h.Totals.AddDef("政治_B+ 政治_A*0.3",AggregateEnum.Average,"政治_折合")
h.Totals.AddDef("历史_A",AggregateEnum.Average,"历史_A卷")
h.Totals.AddDef("历史_B",AggregateEnum.Average,"历史_B卷")
h.Totals.AddDef("历史_B+ 历史_A*0.3",AggregateEnum.Average,"历史_折合")
h.Totals.AddDef("物理_A",AggregateEnum.Average,"物理_A卷")
h.Totals.AddDef("物理_B",AggregateEnum.Average,"物理_B卷")
h.Totals.AddDef("物理_B+ 物理_A*0.35+iif(物理实验 is null,0,物理实验)",AggregateEnum.Average,"物理_折合")
h.Totals.AddDef("化学_A",AggregateEnum.Average,"化学_A卷")
h.Totals.AddDef("化学_B",AggregateEnum.Average,"化学_B卷")
h.Totals.AddDef("化学_B+ 化学_A*0.3+iif(化学实验 Is null,0,化学实验)",AggregateEnum.Average,"化学_折合")
h.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_折合")
h.Totals.AddDef("物理实验",AggregateEnum.Average,"物理_实验")
h.Totals.AddDef("化学实验",AggregateEnum.Average,"化学_实验")
h.Totals.AddDef("生物_",AggregateEnum.Average,"生物_A卷")
h.Totals.AddDef("地理_",AggregateEnum.Average,"地理_A卷")
h.Totals.AddDef("生物_A*0.35",AggregateEnum.Average,"生物_折合")
h.Totals.AddDef("地理_A*0.35",AggregateEnum.Average,"地理_折合")
h.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_B卷")
h.Totals.AddDef("折总",AggregateEnum.Average,"折总平均")
h.Totals.Addexp("折总分段_500以上", "Case When 折总 >= 500 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_460至499", "Case When 折总 >= 460 and 折总<500 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_400至459", "Case When 折总 >= 400 and 折总< 460 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_350至399", "Case When 折总 >= 350 and 折总< 400 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_350以下", "Case When 折总< 350 Then 1 Else 0 End")
h.FromServer = True

 这个地方如何将表2并入表1，再绑定tb3的数据源

tb3.DataSource = g.BuildDataSource()
--  作者：有点蓝
--  发布时间：2022/12/6 21:50:00
--  
分组个数不一样，没有办法直接合并。只能是新增然后查询赋值
--  作者：有点蓝
--  发布时间：2022/12/6 21:51:00
--  
可以试试这种有没有用：http://www.foxtable.com/webhelp/topics/2305.htm
--  作者：刘林
--  发布时间：2022/12/8 18:26:00
--  


Dim h As New sqlGroupTableBuilder("表2", "成绩")
Dim dt1 As fxDataSource
h.FromServer = True
h.C
h.filter ="学段 =\'初中\' and (免统审核 = 0 or 免统审核 is null) and 考试名称 =\'" & ccb1 & "\'"
h.Groups.AddDef("考试名称")
h.Groups.AddDef("单位名称")
h.Groups.AddDef("年级")
h.Totals.AddDef("姓名",AggregateEnum.Count,"参考人数")
h.Totals.AddDef("语文_A",AggregateEnum.Average,"语文_A卷")
h.Totals.AddDef("语文_B",AggregateEnum.Average,"语文_B卷")
h.Totals.AddDef("语文_B+ 语文_A*0.7",AggregateEnum.Average,"语文_折合")
h.Totals.AddDef("数学_A",AggregateEnum.Average,"数学_A卷")
h.Totals.AddDef("数学_B",AggregateEnum.Average,"数学_B卷")
h.Totals.AddDef("数学_B+ 数学_A*0.7",AggregateEnum.Average,"数学_折合")
h.Totals.AddDef("英语_A",AggregateEnum.Average,"英语_A卷")
h.Totals.AddDef("英语_B",AggregateEnum.Average,"英语_B卷")
h.Totals.AddDef("英语_B+ 英语_A*0.7",AggregateEnum.Average,"英语_折合")
h.Totals.AddDef("政治_A",AggregateEnum.Average,"政治_A卷")
h.Totals.AddDef("政治_B",AggregateEnum.Average,"政治_B卷")
h.Totals.AddDef("政治_B+ 政治_A*0.3",AggregateEnum.Average,"政治_折合")
h.Totals.AddDef("历史_A",AggregateEnum.Average,"历史_A卷")
h.Totals.AddDef("历史_B",AggregateEnum.Average,"历史_B卷")
h.Totals.AddDef("历史_B+ 历史_A*0.3",AggregateEnum.Average,"历史_折合")
h.Totals.AddDef("物理_A",AggregateEnum.Average,"物理_A卷")
h.Totals.AddDef("物理_B",AggregateEnum.Average,"物理_B卷")
h.Totals.AddDef("物理_B+ 物理_A*0.35+iif(物理实验 is null,0,物理实验)",AggregateEnum.Average,"物理_折合")
h.Totals.AddDef("化学_A",AggregateEnum.Average,"化学_A卷")
h.Totals.AddDef("化学_B",AggregateEnum.Average,"化学_B卷")
h.Totals.AddDef("化学_B+ 化学_A*0.3+iif(化学实验 Is null,0,化学实验)",AggregateEnum.Average,"化学_折合")
h.totals.AddDef("体育_B",AggregateEnum.Average,"体育_折合")
h.Totals.AddDef("物理实验",AggregateEnum.Average,"物理_实验")
h.Totals.AddDef("化学实验",AggregateEnum.Average,"化学_实验")
h.Totals.AddDef("生物_",AggregateEnum.Average,"生物_A卷")
h.Totals.AddDef("地理_",AggregateEnum.Average,"地理_A卷")
h.Totals.AddDef("生物_A*0.35",AggregateEnum.Average,"生物_折合")
h.Totals.AddDef("地理_A*0.35",AggregateEnum.Average,"地理_折合")
h.Totals.AddDef("体育_B",AggregateEnum.Average,"体育_B卷")
h.Totals.AddDef("折总",AggregateEnum.Average,"折总平均")
h.Totals.Addexp("折总分段_500以上", "Case When 折总 >= 500 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_460至499", "Case When 折总 >= 460 and 折总<500 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_400至459", "Case When 折总 >= 400 and 折总< 460 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_350至399", "Case When 折总 >= 350 and 折总< 400 Then 1 Else 0 End")
h.Totals.Addexp("折总分段_350以下", "Case When 折总< 350 Then 1 Else 0 End")
dt1 = h.BuildDataSource()
Dim tbl As DataTable = dt1.TodataTable()


请问老师，这里提示tabatable() 不是fxDataSource的成员，应该怎样才对
--  作者：有点蓝
--  发布时间：2022/12/8 20:15:00
--  
Foxtable更新到最新版才有TodataTable这个功能
--  作者：刘林
--  发布时间：2022/12/8 20:47:00
--  
为改进效率，现在做了两个统计
tb3.DataSource = g.BuildDataSource()

dim dts as datable = s.Build(True)


tb3  包含dts 所有列，用Merger将dts的所有行合燕到tb3里去这样更快，试了下没行，请问如果这样的方式怎样写代码，谢谢