sql - 检索最高的两个分数

Question

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

科目1 + 科目2 + 科目3 + (科目4, 5, 6最好的2个成绩之和) + (科目7,8,9,10,11最好的2个成绩)

一些科目将无效，因为学生只能选择 7,8,9,10,11 中的任意 3 门

每个考试都由一个 ExamDateID 定义

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

Access 2010

Answer 1

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

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;
   

2. 科目 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;
   

3. 科目 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);

---

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

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