如何按指定字段分组后限制前N组并保留组内所有数据？

这个需求很实用——你希望保留每个(manufacturer_title, series)分组下的全部数据，同时只筛选出前N个分组的内容，而不是像GROUP BY那样只保留每组一行。我们可以用窗口函数来优雅地实现这个目标，具体方案如下：

核心思路 #

1. 先给每一行标记它所属的分组排名：按照c.title（manufacturer_title）和a.series的顺序，给每个唯一的组合分配一个连续的排名。
2. 筛选出排名小于等于你指定限制数的所有行，这样就能得到前N个分组的全部数据。

修改后的查询代码 #

方式一：使用CTE（可读性更高） #

WITH ranked_groups AS (
    SELECT 
        a.item_id, 
        a.title, 
        a.series, 
        c.title AS manufacturer_title,
        -- 按制造商名称和系列排序，给每个唯一分组分配排名
        DENSE_RANK() OVER (ORDER BY c.title, a.series) AS group_rank
    FROM catalog_items AS a 
    JOIN catalog_franchises AS c ON a.manufacturer_id = c.franchise_id 
    JOIN catalog_franchises AS e ON a.game_id = e.franchise_id 
    WHERE e.franchise_id = 6 
      AND a.valid = TRUE 
      AND c.valid = TRUE 
      AND e.valid = TRUE
)
SELECT item_id, series, manufacturer_title
FROM ranked_groups
WHERE group_rank <= 3 -- 替换成你需要的限制数，比如3
ORDER BY manufacturer_title, series, item_id;

方式二：使用子查询（兼容老版本数据库） #

如果你的数据库不支持CTE（比如MySQL 5.7及以前版本），可以用子查询实现同样的效果：

SELECT item_id, series, manufacturer_title
FROM (
    SELECT 
        a.item_id, 
        a.title, 
        a.series, 
        c.title AS manufacturer_title,
        DENSE_RANK() OVER (ORDER BY c.title, a.series) AS group_rank
    FROM catalog_items AS a 
    JOIN catalog_franchises AS c ON a.manufacturer_id = c.franchise_id 
    JOIN catalog_franchises AS e ON a.game_id = e.franchise_id 
    WHERE e.franchise_id = 6 
      AND a.valid = TRUE 
      AND c.valid = TRUE 
      AND e.valid = TRUE
) AS sub_query
WHERE group_rank <= 3 -- 替换成你需要的限制数
ORDER BY manufacturer_title, series, item_id;

代码解释 #

• DENSE_RANK()：这个窗口函数会按照c.title和a.series的排序规则，给每个唯一的分组分配一个连续的排名（比如第一个分组是1，第二个是2，以此类推），相同分组内的所有行都会得到同一个排名。
• 外层查询通过WHERE group_rank <= 3筛选出前3个分组的所有行，完美符合你想要保留分组内全部数据的需求。
• 最后的ORDER BY保持了你原查询的排序逻辑，确保结果顺序和预期一致。

验证结果 #

按照你给出的示例数据，当限制数设为3时，查询会返回：

item_id series manufacturer_title
46      2      fantasy flight games
63      1      gaming heads
64      1      gaming heads
33      2      reaper miniatures

完全匹配你期望的输出。

内容的提问来源于stack exchange，提问作者mister martin