以下是引用有点甜在2014-5-16 11:34:00的发言：
按钮代码
Dim cnames As String = ""
Dim enames As String = ""
Dim onames As String = "准考号, 班级, 座号, 姓名,"
Dim dic As new Dictionary(Of String, String)
For Each dr As DataRow In DataTables("计算公式").Select("考试名称 is not null")
Dim Total As String = ""
For Each ary As String In dr("科目").split(",")
If ary <> "总分" Then
cnames &= "(Select top 1 " & ary & " from {学生成绩库} b where b.准考号 = a.准考号 And b.考试名称 = '" & dr("考试名称") & "') as [" & dr("考试名称") & "_" & ary & "],"
Total &= "isnull([" & dr("考试名称") & "_" & ary & "], 0)+"
onames &= "[" & dr("考试名称") & "_" & ary & "],"
If dic.ContainsKey(ary) = False Then
dic.Add(ary, "isnull([" & dr("考试名称") & "_" & ary & "],0) * " & dr("权重") & "+")
Else
dic(ary) = dic(ary) & "isnull([" & dr("考试名称") & "_" & ary & "],0) * " & dr("权重") & "+"
End If
End If
Next
If dr("科目").contains("总分") Then
onames &= Total.TrimEnd("+") & " as [" & dr("考试名称") & "_总分],"
End If
enames &= "'" & dr("考试名称") & "',"
Next
Dim zfname As String = ""
For Each k As String In dic.Keys
onames &= dic(k).TrimEnd("+") & " As 成绩汇总_" & k & ","
zfname &= dic(k)
Next
onames &= zfname.TrimEnd("+") & " as 成绩汇总_总分"
Dim sql As String = "select " & onames.TrimEnd(",") & " from (Select 准考号, 班级, 座号, 姓名, " & cnames.TrimEnd(",") & " from {学生成绩库} a where 考试名称 In (" & enames.TrimEnd(",") & ") group by 准考号, 班级, 座号, 姓名) As c"
output.show(sql)
Dim q As new QueryBuilder
q.ConnectionName = "学生成绩库"
q.TableName = "成绩汇总"
q.SelectString = sql
q.Build
MainTable = Tables("成绩汇总")
[此贴子已经被作者于2014-5-16 11:35:04编辑过]
再次请教有点甜老师：
因统计的考试成绩是跨学年度的（如7年级、8年级、9年级），学生的座号必然是变化的，这样造成统计后每个学生有多行数据（同样的数据，一个座号一行），而且有的学生因转学、辍学等并没有参加最后一次考试（“计算公式表”中最后一行或第一行的考试名称），不需要统计，
请教：如果学生统计对象（准考号, 班级, 座号, 姓名）只列出参加最后一次考试（“计算公式表”中最后一行或第一行的考试名称）的对象，成绩统计方式不变，上面的代码应如何改？
谢谢！
[此贴子已经被作者于2014-5-29 9:01:36编辑过]
加好友 
发短信
Post By：2014/5/15 23:22:00 [显示全部帖子]
一做下去，就没完没了了...... 