主题：转置后的临时表怎么用

甜老师，给你发一个实例，帮我看看，找不到解决的办法

此主题相关图片如下：qq图片20181030115512.png

 

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:成绩管理.table

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")
Tables("成绩转置").grid.Cols("合计").caption = "总分"
Dim dic As new Dictionary(of String, String)
For Each c As Col In Tables("成绩转置").cols
dic.add(c.caption, c.name)
Next
'---------------------------------
Dim fsd As New List(of Integer)
For Each r1 As Row In Tables("自定义统计分数段").Rows
    If r1.Checked Then
        fsd.add(r1("分数段"))
    End If
Next
If fsd.count> 0 Then
    fsd.sort
    Dim dtb As New DataTableBuilder("统计")
    dtb.AddDef("班级", Gettype(String))
    dtb.AddDef("科目", Gettype(String), 32)
    For Each fs As Integer In fsd
        dtb.AddDef(fs & "分" & iif(fs = fsd(0), "以下","以上") , Gettype(Integer))
    Next
    dtb.Build()
End If

Dim flt As String
Dim dt As DataTable =DataTables("成绩转置")
Dim t1 As Table =Tables("统计")
t1.StopRedraw

Dim bjs As List(of String) = dt.GetUniqueValues("","班级")
For Each flt In bjs
    Dim f As New Filler
    f.SourceTable =DataTables("科目表")
    f.DataTable = t1.DataTable   
    f.Fill()
    t1.DataTable.ReplaceFor("班级",flt,"班级 Is null")
Next

For Each dc As Col In t1.Cols
    If dc.Index > 1 Then
        For Each dr As Row In t1.Rows
            flt = "班级 = '" & dr("班级") & "' And " & dic(dr("科目"))
            If dc.Index =2 Then
                flt+ = " < "  &  dc.name.split("分")(0)
            ElseIf dc.Index =Tables("统计").Cols.count -1 Then
                flt+ = " >= "  &  dc.name.split("分")(0)
            Else
                flt+ = " >= " &  dc.name.split("分")(0) & " And " & dic(dr("科目")) & " < " & t1.Cols(dc.Index+1).Name.split("分")(0)
            End If
            dr(dc.name) =dt.Compute("Count(姓名)",flt)
        Next
    End If
Next

t1.DataTable.SysStyles("EmptyArea").BackColor = Color.White
t1.ResumeRedraw
MainTable = t1

甜老师，如果是这样转置的呢？

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.Totals.AddDef("排名")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")

其中的排名，以及展示成绩分布的【统计】表怎么做？

看懂下面实例

 

http://www.foxtable.com/webhelp/scr/2394.htm

 

http://www.foxtable.com/webhelp/scr/2965.htm

 

合计我会做，怎样排名？

 

 

参考

 

http://www.foxtable.com/webhelp/scr/2382.htm

 

此主题相关图片如下：qq图片20181030175208.png

参考

 

Dim g As New CrossTableBuilder("成绩转置", DataTables("成绩登记表"))
g.HGroups.AddDef("考试期数")
g.HGroups.AddDef("班级")
g.HGroups.AddDef("学号")
g.HGroups.AddDef("姓名")
g.VGroups.AddDef("课程名称")
g.Totals.AddDef("分数", "分数")
g.Totals.AddDef("排名")
g.HorizontalTotal = True
g.Build()
MainTable = Tables("成绩转置")

'获得所有班级名称,保存在集合中
Dim bjs As List(Of String()) = DataTables("成绩转置").GetValues("考试期数|班级")
DataTables("成绩转置").StopRedraw
For Each bj As String() In bjs
    '获得该班级的全部行,按总分降序排序
    For Each c As Col In Tables("成绩转置").Cols
        If c.Caption.Contains("分数") Then
            Dim fs As String = "分数_" & c.name.Split("_")(1)
            Dim pm As String = "排名_" & c.name.Split("_")(1)
            If c.Caption.Contains("合计") Then
                fs = "合计_分数"
                pm = "合计_排名"
            End If
            Dim drs As List(Of DataRow) = DataTables("成绩转置").Select("考试期数 = '" & bj(0) & "' and [班级] = '" & bj(1) & "'", fs & " DESC")
            For n As Integer = 0 To drs.Count - 1 '遍历所有行
                If n > 0 AndAlso drs(n)(fs) = drs(n-1)(fs) Then '如果总分和上一行相同
                    drs(n)(pm) = drs(n-1)(pm) '则排名等于上一行
                Else
                    drs(n)(pm) = n + 1 '设置排名
                End If
            Next
        End If
    Next
Next
DataTables("成绩转置").ResumeRedraw

老甜，完美！！！

太厉害了您，让人羡慕嫉妒

此主题相关图片如下：qq图片20181030183210.png