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("成绩汇总")