如何在行存在重复时按variant首字母分组选取count值最大的多行数据

• 按「ID + variant首字母」为分组维度，筛选每个分组内count值最高的记录
• 最终返回结果保留完整的2位variant编码，不可仅返回首字母
• count值并列时可任选一条，也可全部返回

通用SQL解法（支持PostgreSQL/MySQL 8.0+/Oracle等支持窗口函数的数据库） #

使用窗口函数标记每个分组内的记录排序优先级，筛选排序第一的记录即可：

SELECT ID, Variant, Count
FROM (
    SELECT 
        ID,
        Variant,
        Count,
        -- 按ID+variant首字母分组，count倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY ID, SUBSTR(Variant, 1, 1) 
            ORDER BY Count DESC
        ) AS rn
        -- 如果需要保留所有count并列的记录，把上面的ROW_NUMBER换成RANK()即可
    FROM 你的表名
) t
WHERE rn = 1;

结果说明 #

执行上述查询后，针对你提供的示例数据返回结果如下：

1, a2
1, b1
2, a1
2, b1 -- 若用ROW_NUMBER只返回其中一条并列项，用RANK会同时返回b1和b2

如果你的数据库不支持窗口函数（比如MySQL 5.x及更早版本），可以用关联子查询实现：

SELECT t1.ID, t1.Variant, t1.Count
FROM 你的表名 t1
INNER JOIN (
    SELECT ID, SUBSTR(Variant,1,1) AS first_char, MAX(Count) AS max_count
    FROM 你的表名
    GROUP BY ID, SUBSTR(Variant,1,1)
) t2 ON t1.ID = t2.ID 
    AND SUBSTR(t1.Variant,1,1) = t2.first_char 
    AND t1.Count = t2.max_count;

该版本默认返回所有count并列的记录，如需去重可额外增加过滤逻辑。

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