成绩分布 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

请老师指教，数据表可以统计成绩分布，查询表不行。

下载信息 [文件大小：下载次数： ]
点击浏览该文件:成绩表.rar

AddExp是SQLGroupTableBuilder才有的功能，GroupTableBuilder是没有的

可以这样

Dim t As Table = e.Form.controls("Table1").Table

Dim b As New sqlGroupTableBuilder("统计表1","Select 班级,姓名,科目,分数 From {成绩表}","")

b.Groups.AddDef("班级")

b.Groups.AddDef("科目")

b.Totals.AddDef("分数",AggregateEnum.Max,"最高分")

b.Totals.AddDef("分数",AggregateEnum.Min,"最低分")

b.Totals.AddDef("分数",AggregateEnum.Average,"平均分")

b.Totals.AddExp("分布_60以下","iif(分数 < 60,1,0)")

b.Totals.AddExp("分布_60-70","iif(分数 >= 60 And 分数 < 70,1,0)")

b.Totals.AddExp("分布_70-80","iif(分数 >= 70 And 分数 < 80,1,0)")

b.Totals.AddExp("分布_80-90","iif(分数 >= 80 And 分数 < 90,1,0)")

b.Totals.AddExp("分布_90-100","iif(分数 >= 90 And 分数 < 100,1,0)")

b.Totals.AddExp("分布_100-110","iif(分数 >= 100 And 分数 < 110,1,0)")

b.Totals.AddExp("分布_110-120","iif(分数 >= 110 And 分数 < 120,1,0)")

b.Totals.AddExp("分布_120-130","iif(分数 >= 120 And 分数 < 130,1,0)")

b.Totals.AddExp("分布_130-140","iif(分数 >= 130 And 分数 < 140,1,0)")

b.Totals.AddExp("分布_140以上","iif(分数 >= 140,1,0)")

'b.build() ‘这一句去掉，完全多余

t.DataSource = b.BuildDataSource()

老师想求优秀率？请老师指教，谢谢！

Dim t As Table = e.Form.controls("Table1").Table
e.Form.controls("NavBar1").SelectedPage.Butt
Dim b As New sqlGroupTableBuilder("统计表1","Se lect c.学期,班级,考试名称,c.学生编号,学生姓名,学科,卷面分数,学生分数,Round(学生分数/卷面分数*100,2) As 折合分数 Fr om ({学生基本信息} a Inner JOIN {学生缴费} b ON b.学生编号 = a.学生编号) Inner JOIN {学生成绩} c ON c.学期 = b.学期 And c.学生编号 = b.学生编号 Where c.学期 = '20191' And 考试名称 = '抽考'","")
b.Groups.AddDef("班级")
b.Groups.AddDef("学科")
b.Totals.AddDef("学生编号",AggregateEnum.Count,"学生数")
b.Totals.AddDef("学生分数",AggregateEnum.Max,"最高分")
b.Totals.AddDef("学生分数",AggregateEnum.Min,"最低分")
b.Totals.AddDef("学生分数",AggregateEnum.Sum,"总分")
b.Totals.AddDef("学生分数",AggregateEnum.Average,"平均分")
b.Totals.AddExp("参考数","iif(学生分数 Is Not Null,1,0)")
b.Totals.AddExp("缺考数","iif(学生分数 Is Null,1,0)")
b.Totals.AddExp("优秀数","iif(Left(班级,1) = '1' And 折合分数 > 90,1,iif(Left(班级,1) = '2' And 折合分数 > 85,1,iif((Left(班级,1) = '3' Or Left(班级,1) = '4' Or Left(班级,1) = '5' Or Left(班级,1) = '6') And 折合分数 > 80,1,iif((Left(班级,1) = '7' Or Left(班级,1) = '8' Or Left(班级,1) = '9') And 折合分数 > 75,1,0))))")
b.Totals.AddExp("及格数","iif(学生分数 >= 60,1,0)")
b.Totals.AddExp("不及格数","iif(学生分数 < 60,1,0)")

b.Totals.AddExp("优秀率","iif(Left(班级,1) = '1' And 折合分数 > 90,1,iif(Left(班级,1) = '2' And 折合分数 > 85,1,iif((Left(班级,1) = '3' Or Left(班级,1) = '4' Or Left(班级,1) = '5' Or Left(班级,1) = '6') And 折合分数 > 80,1,iif((Left(班级,1) = '7' Or Left(班级,1) = '8' Or Left(班级,1) = '9') And 折合分数 > 75,1,0))))/iif(学生分数 Is Not Null,1,0)")

b.Totals.AddExp("学生分数分布_60以下","iif(学生分数 < 60,1,0)")
b.Totals.AddExp("学生分数分布_60-70","iif(学生分数 >= 60 And 学生分数 < 70,1,0)")

t.DataSource = b.BuildDataSource()

按学科排名，求代码！谢谢

下载信息 [文件大小：下载次数： ]
点击浏览该文件:成绩分布.rar

[此贴子已经被作者于2020/1/10 7:43:04编辑过]

http://foxtable.com/bbs/dispbbs.asp?boardid=2&Id=145057&page=2&star=3

主题：成绩分布