sql - MS Access 选择按多个字段分组的前 n 个查询

Question

这是一个问题的第 2 部分，该问题已由该板上的 peter 回答。再次感谢佩特姆!

所以我有代码可以返回给定学生的前 3 名考试成绩。我的表如下所示:

学生 ID、测试 ID、分数
1,1, 95
1、2、90
1、3、90
1、4、90
2, 1, 99
2, 2, 95
2、3、90
2、4、90

感谢peterm，我有以下代码可以为我做到这一点:

SELECT StudentID, TestID, TestScore
  FROM MyTable t
 WHERE TestID IN
(
  SELECT TOP 3 TestID 
    FROM MyTable
   WHERE StudentID = t.StudentID 
   ORDER BY TestScore DESC, TestID
)
 ORDER BY StudentID, TestScore DESC, TestID;

我的新问题是现在我需要向表中添加两个新字段，用于科目和年份，因此我需要找到每个科目 - 学生 - 年组合的前 3 个分数。一旦我获得了每个组合的前 3 名分数，我需要对它们求平均值，以便每个学生 - 学科年组合的前 3 名分数的平均分。希望我已经足够清楚地解释了这一点，而不必模拟另一张 table 。

提前致谢。

Answer 1

你可以做这样的事情

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER BY TestScore DESC, TestID
  )
) q
 GROUP BY StudentID, Year, Subject
 ORDER BY StudentID, Year, Subject;

示例输出:

|学生证 |年 |主题 |平均分 |
|-----------|------|---------|---------|
| 1 | 2012 | 1 | 91 |
| 1 | 2012 | 2 | 84 |
| 2 | 2012 | 1 | 94 |
| 2 | 2012 | 3 | 95 |

这是 SQLFiddle 演示。
演示通常用于 SQL Server，但预计可在 MS Access 中使用，可能需要稍作语法调整