按cl_id分组选取最大ratio记录，平局时取最大diff的SQL实现

需求 #

现有表包含id、cl_id、ratio、diff列，需按cl_id分组执行以下筛选：

• 选取每组中ratio值最大的记录
• 若同一组内存在多条ratio值相同的记录，从中选取diff值最大的条目

示例表 #

原始表（Have） #

CREATE TABLE Have(
    id INT PRIMARY KEY,
    cl_id CHAR(1),
    ratio DECIMAL(5,4),
    diff INT
);

INSERT INTO Have(id, cl_id, ratio, diff) VALUES 
(122222, 'a', 1.3333, 2),
(123333, 'a', 1.3333, 5),
(124444, 'b', 1.25, 2),
(125555, 'b', 1.6667, 1);

目标结果表（Want） #

CREATE TABLE Want(
    id INT PRIMARY KEY,
    cl_id CHAR(1),
    ratio DECIMAL(5,4),
    diff INT
);

INSERT INTO Want(id, cl_id, ratio, diff) VALUES 
(123333, 'a', 1.3333, 5),
(125555, 'b', 1.6667, 1);

解决方案 #

方法1：窗口函数（高效直观） #

利用ROW_NUMBER()窗口函数按分组规则排序，筛选每组排名第1的记录即可：

SELECT id, cl_id, ratio, diff
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY cl_id 
            ORDER BY ratio DESC, diff DESC
        ) AS rn
    FROM Have
) ranked
WHERE rn = 1;

逻辑说明：

• PARTITION BY cl_id 按分组字段拆分数据集
• ORDER BY ratio DESC, diff DESC 先按ratio降序排序，再按diff降序排序，确保符合需求的优先级
• 筛选rn=1得到每组唯一的目标记录

方法2：关联子查询（兼容旧版数据库） #

若数据库不支持窗口函数，可通过子查询获取每组的最大ratio和对应最大diff，再关联原表筛选结果：

SELECT h.id, h.cl_id, h.ratio, h.diff
FROM Have h
JOIN (
    SELECT 
        cl_id,
        MAX(ratio) AS max_ratio,
        MAX(diff) AS max_diff
    FROM Have
    GROUP BY cl_id
) group_vals 
    ON h.cl_id = group_vals.cl_id
    AND h.ratio = group_vals.max_ratio
    AND h.diff = group_vals.max_diff;

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