Access 2002-如何在同一查询中对分组依据和前N条记录进行分组 [英] Access 2002 - how to Group By and Top N records in same query

问题描述

我的数据如下所示:

Group     Product    PercentAch
Gr1       Pr1        55%
Gr1       Pr2        65%
Gr2       Pr1        45%
Gr2       Pr2        95%
Gr3       Pr1        15%
Gr3       Pr2        35%
.
.
.

因此，基本上，数据描述了分配给不同组的一组不同的产品.我正在尝试创建一个查询，该查询将为我检索按产品分组的PercentAch的前N％个记录. Access帮助文件说要按PercentAch排序才能使用TOP属性，但是这会使组和产品混合在一起.我的另一个选择是为每个组/产品组合创建一个单独的查询，这似乎是多余的.是否可以仅使用一个(或两个)查询来执行此操作?

So basically the data describe a set of distinct products that are assigned to different groups. I am trying to create a query that will retrieve for me the top N% of records based on PercentAch by product by group. The Access help file says to sort by PercentAch in order to use the TOP property, but then that causes groups and products to be intermingled. My other option is to create a separate query for each Group/Product combination, which seems redundant. Is there a way to do this using just one (or two) queries?

推荐答案

您需要使用唯一的标识符，否则，如果同一组中的多个产品具有相同的PercentAch，则将获得所有这些产品……更多比您想要的前5％高.假设我们在产品上具有唯一的ID. SQL将是:

You need to use a unique identifier otherwise if you have multiple products in the same group with the same PercentAch you will get all of those products ... ie more than the top 5% you wanted. Assume we have a unique ID on the Product. The SQL will be:

SELECT Group, ProductID, Product, PercentAch
FROM SalesPerformance
WHERE ProductID IN (
    SELECT TOP 5 PERCENT ProductID
    FROM SalesPerformance as S
    WHERE S.Group = SalesPerformance.Group
    ORDER BY PercentAch DESC
);

这篇关于Access 2002-如何在同一查询中对分组依据和前N条记录进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！