MySQL如何按RBI值打破HR列并列并删除分组下多余行

实现方案 #

方案1：MySQL 8.0及以上版本（推荐，使用窗口函数）

通过ROW_NUMBER()窗口函数按年份+联赛ID分组排序，直接取每组排名第一的记录即可：

-- 仅查询结果，不修改原表
WITH ranked_stats AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(
            PARTITION BY year, lgID 
            ORDER BY HR DESC, RBI DESC
        ) AS rank_num
    FROM 你的表名
)
SELECT year, lgID, playerID, HR, RBI 
FROM ranked_stats 
WHERE rank_num = 1;

如果需要直接删除原表中多余的记录，可使用以下语句（操作前建议备份数据）：

DELETE t1
FROM 你的表名 t1
INNER JOIN 你的表名 t2
    ON t1.year = t2.year 
    AND t1.lgID = t2.lgID 
    AND (t1.HR < t2.HR OR (t1.HR = t2.HR AND t1.RBI < t2.RBI));

方案2：MySQL 5.x版本（无窗口函数支持）

可通过关联子查询实现同样效果：

SELECT t1.year, t1.lgID, t1.playerID, t1.HR, t1.RBI
FROM 你的表名 t1
WHERE NOT EXISTS (
    SELECT 1 FROM 你的表名 t2
    WHERE t2.year = t1.year 
      AND t2.lgID = t1.lgID 
      AND (t2.HR > t1.HR OR (t2.HR = t1.HR AND t2.RBI > t1.RBI))
);

逻辑说明 #

• 分组规则：以year（年份）和lgID（联赛ID）为一个分组，每个分组最终仅保留1行记录
• 排序规则：同分组内优先按HR（本垒打）数值降序排列，HR数值相同时按RBI（打点）数值降序排列，取排序后第一位即为需保留的记录
• 验证效果：针对你给出的1934年测试数据，AL分组中HR最高的是数值为49的gehrilo01、NL分组中HR同为35时RBI更高的是ottme01，和预期输出完全一致。

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