MS Access选择按多个字段分组的前n个查询 [英] MS Access Select top n query grouped by multiple fields

问题描述

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

This is part 2 of a problem that was already answered by peterm on this board. Thanks again peterm!

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

So I have code that will return the top 3 test scores for a given student. My table looks like the following:

学生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

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，我有下面的代码将为我做到这一点:

Thanks to peterm, I have the following code which will do this for me:

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

My new problem is now I need to add two new fields to the table for Subject and Year, so I need to find the top 3 scores for each Subject-Student-Year combination. Once I have the top 3 scores for each combination, I need to average them so that I will have one averaged score of the top 3 scores for each student-subject-year combination. Hopefully, I've explained this clearly enough without having to mock up another table.

先谢谢了.

推荐答案

您可以执行以下操作

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 |

这里是 SQLFiddle 演示.
演示通常用于SQL Server，但有望在MS Access中运行，也许需要进行一些语法上的调整

Here is SQLFiddle demo.
Demo as usually is for SQL Server but expected to work in MS Access, maybe with minor syntactic tweaks

这篇关于MS Access选择按多个字段分组的前n个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！