为每组选择前 N 行 [英] Select top N rows for each group

问题描述

我有以下 MS Access DB 架构:

I have the following MS Access DB schema:

我想从按 Items.score 排序的 Items 表中选择行，以便每个组最多有 Group.top_count 行.

I want to select rows from Items table ordered by Items.score so that there are at most Group.top_count rows for each group.

例如我在表中有以下数据:

For example I have the following data in the tables:

组表:

物品表:

我想为第 1 组选择前 2 个项目，为第 2 组选择前 1 个项目.所以结果必须包含第 1、2 和 5 行.

I want to select top 2 items for group #1 and top 1 item for group #2. So the result must contain rows 1, 2 and 5.

DBA stackexchange 有一个类似的问题，但关于 SQL Server.所以所有答案都使用 SQL Server 语法，我无法将其调整为在 MS Access 上工作.

There was a similar question at DBA stackexchange, but about SQL Server. So all answers used SQL Server syntax and I couldn't adapt it to work on MS Access.

推荐答案

如果每组有一个固定的人数，你可以这样做:

If there were a constant number per group, you could do:

select i.*
from items as i inner join
     groups as g
     on i.group_id = g.id
where i.id in (select top 2 i2.id
               from items i2
               where i2.group_id = i.group_id
               order by i2.score desc
              );

相反，您需要枚举值，这在 MS Access 中很昂贵:

Instead, you will need to enumerate the values and this is expensive in MS Access:

select i.*
from (select i.*,
             (select count(*)
              from items i2
              where i2.group_id = i.group_id and
                    (i2.score < i.score or
                     i2.score = i.score and i2.id <= i2.id
                    )
             ) as seqnum
      from items as i
     ) as i inner join
     groups as g
     on i.group_id = g.id
where i.seqnum <= g.top_count;

这个逻辑实现了等价于row_number()，这是解决这个问题的正确方法(如果你的数据库支持).

This logic implements the equivalent of row_number(), which is the right way to solve this problem (if your database supports it).

这篇关于为每组选择前 N 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！