每个组查询的前3个值MS Access [英] Top 3 values per group query MS Access

问题描述

我是MS访问的新手，我试图进行一次查询，以将3个不同类别的前3名人员的得分调高，即所需的结果是:

im new to MS access, and im trying to make a query that will pull up the top 3 people in 3 different categories in terms of points, i.e the desired outcome is :

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

我已经找到了一些这样的代码，我/认为/可以做到，

Ive found bits of code like this, which i /think/ does it,

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;

但是我不知道这意味着什么，更不用说如何使其适应我的需求了. 外面有人对如何获得理想的想法有想法吗?

But i have no idea what this means, let alone how to adapt it to fit my needs. Does anyone out there have an idea on how to get the desired out come?

在版本中添加了语法错误.

subbed in version that pulls up a syntax error.

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无关，但结局很棒:)

P.S Unrelated, but The finale was amazing :)

推荐答案

考虑一个相关的子查询，该查询可以计算顺序等级计数，然后可以将其用作派生表来选择前三名:

Consider a correlated sub query that calculates an ordinal rank count which you can then use as a derived table to select top three:

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

要具体说明， GroupRank 是从子查询(列部分中的嵌套选择)中计算出来的，该子查询为外部查询的每个 MembershipType 排名 TestScores .但是，这还不够，因为您将要使用此计算出的 GroupRank 来选择前三名.因此，将整个查询嵌套在FROM子句中，该子句称为派生表，因为您创建了隐式表以返回另一个结果集.此最终结果集将对前3名进行过滤，然后为每个会员资格订购 TestScores .

To explain specifically, GroupRank is calculated from a subquery (nested select in column section) that ranks TestScores for each MembershipType of outer query. However, this is not enough as you will want to use this calculated GroupRank to pick top three. So nest entire query inside a FROM clause which is known as a derived table as you created an implicit table to return another resultset. This final resultset filters for top 3 and then orders TestScores for each Membership.

在MS Access中，您可以将整个FROM子句查询另存为自己的存储查询，然后使用该查询筛选前三名:

In MS Access, you can save entire FROM clause query as its own stored query and then use that query to filter for top three:

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

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

For multiple tables, use table aliases to help which temporarily renames table sources for easier referencing:

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

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