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

小编典典

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

sql

这是该板上的peterm已解决的问题的第2部分。再次感谢peterm！

因此，我有一些代码可以返回给定学生的前3个测试成绩。我的表如下所示：

StudentID, Test ID, Score
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个分数中的一个平均分数。希望我已经足够清楚地解释了这一点，而不必模拟另一个表。

提前致谢。

阅读 287

2021-04-15

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

样本输出：

| STUDENTID | YEAR | SUBJECT | AVGSCORE |
|-----------|------|---------|----------|
|         1 | 2012 |       1 |       91 |
|         1 | 2012 |       2 |       84 |
|         2 | 2012 |       1 |       94 |
|         2 | 2012 |       3 |       95 |

2021-04-15