检索最高的两个分数

Question

为了计算考试的平均分数，我需要得到以下结果：

subject1 +科目2+ subject3 +(科目4、5、6的最佳2分之和)+(科目7、8、9、10、11)

有些科目为零，因为学生只取7,8,9,10,11中的任何3门。

每一次考试都由ExamDateID定义

我怎样才能做到这一点？(我需要结果来填充一个新的桌子.而不是这个问题的范围)

2010年准入

​

​

Answer 1

考虑使用最后一个查询中引用的三个源查询。请注意，在相同的ExamDateID和Subject范围内的标记将在计算中进行汇总：

1. 科目1-3的考试标记(使用条件聚合) 选择e.StudentID，e.ExamDateID，SUM(IIF(e.Subject介于1~ 3，e.Marks，NULL)为SumMarks123，从ExamTable中选择e.StudentID，e.ExamDateID作为e组；
2. 科目4-6的考试标记(最高2)(使用子查询) 选择e.StudentID，e.ExamDateID，SUM(e.Marks)作为SumTop2Marks456，从ExamTable中选择e.Subject在4~6之间，从ExamTable子中选择Count(*)，sub.StudentID = e.StudentID，sub.ExamDateID = e.ExamDateID，sub.Subject在4~6之间，sub.Marks >= e.Marks，<= 2组；
3. 科目考试标记7-11 (最高2个)(使用子查询) 选择e.StudentID，e.ExamDateID，SUM(e.Marks)作为SumTop2Marks711，从ExamTable选择e.Subject在7~ 11之间，从ExamTable子中选择Count(*)，sub.StudentID = e.StudentID和sub.ExamDateID = e.ExamDateID，sub.Subject在7~ 11，sub.Marks >= e.Marks) <= 2组；

最终查询

SELECT a.StudentID, a.ExamDateID, 
       (a.SumMarks123 + b.SumTop2Marks456 + c.SumTop2Marks711) As SumScore
FROM (ExamAvgSubj123Q a 
INNER JOIN ExamAvgSubj456Q b 
    ON (a.ExamDateID = b.ExamDateID) AND (a.StudentID = b.StudentID)) 
INNER JOIN ExamAvgSubj711Q c 
    ON (a.ExamDateID = c.ExamDateID) AND (a.StudentID = c.StudentID);

当然，没有任何理由，您不能将所有查询合并为一个查询，但正如所见，使用派生表进行维护可能会有点紧张：

SELECT a.StudentID, a.ExamDateID, 
       (a.SumMarks123 + b.SumTop2Marks456 + c.SumTop2Marks711) As SumScore    
FROM
   ((SELECT e.StudentID, e.ExamDateID, 
            SUM(IIF(e.Subject BETWEEN 1 AND 3, e.Marks, NULL)) AS SumMarks123
    FROM ExamTable AS e
    GROUP BY e.StudentID, e.ExamDateID) a 

INNER JOIN 
  (SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks456
   FROM ExamTable AS e
   WHERE e.Subject BETWEEN 4 AND 6 
   AND  (SELECT Count(*) FROM ExamTable sub
         WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
         AND sub.Subject BETWEEN 4 AND 6 AND sub.Marks >= e.Marks)  <= 2
   GROUP BY e.StudentID, e.ExamDateID) b 

ON (a.ExamDateID = b.ExamDateID) AND (a.StudentID = b.StudentID)) 

INNER JOIN 
  (SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks711
   FROM ExamTable AS e
   WHERE e.Subject BETWEEN 7 AND 11
   AND  (SELECT Count(*) FROM ExamTable sub
         WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
         AND sub.Subject BETWEEN 7 AND 11 AND sub.Marks >= e.Marks)  <= 2
   GROUP BY e.StudentID, e.ExamDateID) c 

ON (a.ExamDateID = c.ExamDateID) AND (a.StudentID = c.StudentID);

Answer 2

编辑：添加了两个最好的分数

我建议您在button_click事件中使用VBA。下面是代码的一般大纲。您需要改进它：

dim rs as DAO.recordset
dim db as dao.database
set db = current db
dim qry as string
dim avgScore as double
avgScore = 0
numberOfSubject = 0

dim firstBestScore2in3 as double, firstBestScore2in3 as double
firstBestScore2in3 = 0
secondBestScore2in3 = 0

dim firstBestScore2in5 as double, firstBestScore2in5 as double
firstBestScore2in5 = 0
secondBestScore2in5 = 0

'query your database
qry = _
"SELECT studentID, subject, marks " & _
"FROM exams " & _
"WHERE studentID = " & Me.txtStudentID & " " & _
"ORDER BY stubject;"
set rs = db.opendynaset qry

'Iterate through recordset
Do While Not rs.EOF Then

 Select Case rs!subject
  Case 1 To 3
   avgScore = avgScore + rs!subject
  Case 4 To 6
   if (rs!subject > firstBestScore2in3) then
     firstBestScore2in3 = rs!subject
   elseif (rs!subject > secondBestScore2in3) then
     secondBestScore2in3 = rs!subject
   end if
  Case 7 To 11
   if (rs!subject > firstBestScore2in5) then
     firstBestScore2in5 = rs!subject
   elseif (rs!subject > secondBestScore2in5) then
     secondBestScore2in5 = rs!subject
   end if
 end case

 rs.next
loop

avgScore = avgScore + firstBestScore2in3 + secondBestScore2in3 + firstBestScore2in5 + secondBestScore2in5

avgScore = avgScore / 7