MS Access Max和选择前n个查询值重复的问题 [英] MS Access Max and Select top n query issue with duplicate values

问题描述

这是我的第一篇文章，希望我没有违反任何规则.

This is my very first post, so hopefully I did not break any rules.

我是MS Access新手，我的SQL知识充其量是有限的.我正在尝试编写一个查询，该查询将返回给定学生的前3个测试成绩.我的表如下所示:

I am an MS Access newbie and my sql knowledge is limited at best. I am trying to write a query that will return the top 3 test scores for a given student. My tables looks like the following:

Student ID    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

通过在该论坛上进行研究，我已经找到了一些非常有用的代码，这些代码基本上可以在我拥有多个相同价值的测试分数时，为我寻找的东西.在这种情况下，我获得的得分超过前三名.因此，对于学生#1，该查询将返回所有4个测试分数，而不仅仅是前3个.我尝试添加唯一的索引字段进行排序，但运气不佳.到目前为止，我的代码如下:

Through research on this forum, I've been able to find some very helpful code that basically does what I'm looking for EXCEPT when I have multiple test scores that are the same value. In that situation, I get more than the top 3 scores. So in the case of student #1, the query will return all 4 test scores, not just the first 3. I tried adding a unique index field to order by but had no luck. So far, my code is as follows:

SELECT MyTable.StudentID, MyTable.TestID, Max(MyTable.TestScore) AS MaxOfTestScore
FROM MyTable
GROUP BY MyTable.StudentID, MyTable.TestID, MyTable.TestScore
HAVING MyTable.TestScore In (SELECT TOP 3 TestScore FROM MyTable T2 WHERE 
MyTable.StudentID = T2.StudentID ORDER BY TestScore DESC)
ORDER BY MyTable.StudentID, MyTable.TestScore DESC;

到目前为止，Credit都由Sarkazein提供此代码.我试图对此线程发表评论，但由于没有任何声望点而无法发表评论.

Credit goes to Sarkazein for providing this code so far. I tried to comment on that thread but couldn't since I don't have any reputation points.

推荐答案

您是否正在寻找类似的东西?

Are you looking for something like this?

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;

输出:

| STUDENTID | TESTID | TESTSCORE |
|-----------|--------|-----------|
|         1 |      1 |        95 |
|         1 |      2 |        90 |
|         1 |      3 |        90 |
|         2 |      1 |        99 |
|         2 |      2 |        95 |
|         2 |      3 |        90 |

这里是 SQLFiddle 演示. 它用于SQL Server，但我相信应该可以在MS Access中工作

更新它已经在MS Access中进行了测试，并且可以按预期运行，而无需进行任何修改.

UPDATE It's been tested on MS Access and it works as expected without any modifications.

这篇关于MS Access Max和选择前n个查询值重复的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！