每组查询的前 3 个值 MS Access

我是 MS Access 的新手，我正在尝试进行一个查询，该查询将根据分数提取 3 个不同类别中的前 3 名人员，即期望的结果是：

Child's name | Membership Type | Total Points
=============================================
Jon Snow     | Senior          | 12
Hodor        | Senior          | 13
Bran Stark   | Senior          | 67
Cersei       | Intermediate    | 14
Joffery      | Intermediate    | 19
Ramsay Bolton| Intermediate    | 25
Wun-Wun      | Junior          | 14
Arya Stark   | Junior          | 64
Ned Stark    | Junior          | 125

我发现了一些像这样的代码，我/认为/是这样做的，

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;

但我不知道这意味着什么，更不用说如何调整它来满足我的需要了。 有没有人知道如何获得所需的结果？

编辑：在引发语法错误的版本中进行替换。

SELECT [Members.Childs Name], [Members.Membership Type], [Results.Total Points]
FROM 
(SELECT [Members.Childs Name], [Members.Membership Type], [Results.Total Points],
        (SELECT Count(*) FROM [Results], [Members] sub
         WHERE sub.Total Points <= Results.Total Points
         AND sub.Membership Type = Members.Membership Type)  As GroupRank
FROM Members, Results t) As main
WHERE main.GroupRank <= 3
ORDER BY [main.Membership Type],[main.Total Points DESC]

P.S 无关，但结局很棒:)

考虑一个相关子查询，它计算序数排名计数，然后您可以将其用作派生表来选择前三个：

SELECT main.StudentID, main.MembershipType, main.TestScore
FROM 
    (SELECT t.StudentID, t.MembershipType, t.TestScore,
            (SELECT Count(*) FROM MyTable sub 
             WHERE sub.TestScore >= t.TestScore
             AND sub.MembershipType = t.MembershipType) As GroupRank
     FROM MyTable t) As main
WHERE main.GroupRank <= 3
ORDER BY main.MembershipType, main.TestScore DESC

具体解释一下，集团排名从排名的子查询（列部分中的嵌套选择）计算测试成绩对于每个会员类型的外部查询。然而，这还不够，因为您需要使用这个计算得出的集团排名选出前三名。所以将整个查询嵌套在一个FROM当您创建隐式表以返回另一个结果集时，该子句称为派生表。最终结果集过滤前 3 个，然后排序测试成绩对于每个会员资格.

在 MS Access 中，您可以保存整个FROM子句查询作为其自己的存储查询，然后使用该查询来过滤前三个：

SELECT g.StudentID, g.MembershipType, g.TestScore
FROM GroupRankQuery g    
WHERE g.GroupRank <= 3
ORDER BY g.MembershipType, g.TestScore DESC

对于多个表，使用表别名来帮助临时重命名表源以便于引用：

SELECT main.[Childs Name], main.[Membership Type], main.[Total Points] 
FROM 
   (SELECT m.[Childs Name], m.[Membership Type], r.[Total Points], 
           (SELECT Count(*) FROM [Results] subR 
            INNER JOIN [Members] subM ON subR.StudentID = subM.StudentID
            WHERE subR.[Total Points] >= r.[Total Points] 
            AND subM.[Membership Type] = m.[Membership Type]) As GroupRank 
    FROM Results r
    INNER JOIN Members m ON r.StudentID = m.StudentID) As main 
WHERE main.GroupRank <= 3 
ORDER BY main.[Membership Type], main.[Total Points] DESC