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

所以我的代码将返回给定学生的前三个测试分数.我的表如下所示：

学生ID,考试ID,分数
1,1,95
1,2,90
1,3,4,90
2,99
2,95
2,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个分数的平均分数.希望,我已经足够清楚地解释了这一点,而无需模拟另一张桌子.

提前致谢.

解决方法

SELECT StudentID,Year,Subject,AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID,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,Subject
 ORDER BY StudentID,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中运行,可能需要进行少量的语法调整

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!