取得最高的两个分数 [英] Retrieve highest two scores

问题描述

要计算考试的平均分数，我需要获得以下结果:

To calculate the average score in an exam I need to get the results for:

主题1 +主题2 +主题3 +(主题4、5、6的最佳2分之和)+(主题7、8、9、10、11的最佳2分)

subject1 + subject 2 + subject3 + (sum of best 2scores of subject 4, 5, 6) + (Best 2 scores in subject 7,8,9,10,11)

某些科目将为空，因为学生仅需学习7,8,9,10,11中的任意3

some of the subjects will have null as a student only takes any 3 out of 7,8,9,10,11

每项考试均由ExamDateID定义

Each exam is defined by an ExamDateID

我该如何实现? (我需要结果来填充新表...不是这个问题的范围.)

How do I achieve this? (I need the result to populate a new table... Not the scope of this question)

Access 2010

Access 2010

推荐答案

考虑在最终查询中使用三个源查询.请注意，在相同的 ExamDateID 和 Subject 范围内绑定的标记将在计算中汇总:

Consider using three source queries to be referenced in a final query. Do note, marks that tie in same ExamDateID and Subject range will be summed up in calculations:

1. 对象1-3的考试成绩(使用条件汇总)

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;

主题4-6(最高两个)的考试成绩(使用子查询)

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;

主题7-11的考试成绩(最高两个)(使用子查询)

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;

最终查询

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);

---

当然没有理由，您不能将所有查询合并为一个查询，但是可以看出，使用派生表进行维护可能会有些紧张:

---

Of course there is no reason, you cannot combine all queries into one but as seen can be a bit intense to maintain using derived tables:

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);

这篇关于取得最高的两个分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！