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

问题描述

这是一个问题的第 2 部分，peterm 已经在这个板上回答了这个问题.再次感谢彼得！

所以我有代码可以返回给定学生的前 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从 MyTable tTestID 在哪里(选择前 3 个测试 ID从我的表哪里学生ID = t.StudentIDORDER BY TestScore DESC, TestID)ORDER BY StudentID, TestScore DESC, TestID;

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

提前致谢.

解决方案

你可以这样做

SELECT StudentID, Year, Subject, AVG(TestScore) AS AvgScore从(选择学生 ID、年份、学科、测试分数从 MyTable tTestID 在哪里(选择前 3 个测试 ID从我的表哪里学生ID = t.StudentIDAND 年 = t.YearAND 主题 = t.SubjectORDER BY TestScore DESC, TestID)) qGROUP BY StudentID, Year, SubjectORDER 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 中使用，可能需要稍微调整一下语法

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

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

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

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;

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.

Thanks in advance.

解决方案

You can do something like this

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;

Sample output:

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

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屋！