如何为每个country和serial唯一组合获取最多2条记录？

现有表数据如下：

id country serial other_column
1  us      123    1
2  us      456    1
3  gb      123    1
4  gb      456    1
5  jp      777    1
6  jp      888    1
7  us      123    2
8  us      456    3
9  gb      456    4
10  us     123    1
11  us     123    1

需求：为每个country和serial的唯一组合，获取最多2条对应行（组合记录数≥2时取2条，不足则取全部），不能使用select distinct country, serial from my_table（仅返回唯一组合，无法保留多条行）。

主流数据库方案（支持窗口函数：MySQL 8.0+/PostgreSQL/Oracle/SQL Server等） #

使用ROW_NUMBER()窗口函数对每个组合内的行编号，筛选编号≤2的行即可：

SELECT country, serial, other_column
FROM (
    SELECT 
        country, 
        serial, 
        other_column,
        -- 按country+serial分组，组内按id排序并编号
        ROW_NUMBER() OVER (PARTITION BY country, serial ORDER BY id) AS row_num
    FROM my_table
) AS ranked_data
WHERE row_num <= 2;

说明： #

• PARTITION BY country, serial：将数据按country和serial的组合拆分分组
• ORDER BY id：确保组内行按id顺序排列（可替换为其他排序字段，如other_column，按需调整）
• 外层查询筛选每组内编号≤2的行，实现每个组合最多保留2条记录

兼容低版本数据库（如MySQL 5.x，不支持窗口函数） #

使用关联子查询统计同组合内当前行的序号，筛选前2条：

SELECT t1.country, t1.serial, t1.other_column
FROM my_table t1
WHERE (
    SELECT COUNT(*)
    FROM my_table t2
    WHERE t2.country = t1.country
      AND t2.serial = t1.serial
      AND t2.id <= t1.id
) <= 2
ORDER BY t1.country, t1.serial, t1.id;

说明： #

• 子查询统计同country+serial组合中，id小于等于当前行id的记录数，即当前行在组合内的序号
• 筛选序号≤2的行，达到每个组合最多保留2条的效果

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