如何实现按GROUPCOL分组后VAL优先、ID次之的MAX取值SQL查询？

需要按GROUPCOL字段分组，组内优先按VAL字段排序取最大值对应的记录；当VAL为空时，则按ID字段取最大值对应的记录。现有基础SQL仅能获取MAX(VAL)，无法处理VAL为空时的次要条件逻辑。

示例数据 #

期望返回结果 #

解决方案 #

直接用GROUP BY很难准确关联到对应的ID，推荐用窗口函数实现分组内的排序筛选，以下是主流数据库的实现方式：

方式1：ROW_NUMBER()窗口函数（支持MySQL 8.0+、PostgreSQL、SQL Server等）

SELECT ID, VAL, GROUPCOL
FROM (
    SELECT 
        ID, 
        VAL, 
        GROUPCOL,
        ROW_NUMBER() OVER (
            PARTITION BY GROUPCOL 
            ORDER BY 
                VAL DESC NULLS LAST,  -- 优先按VAL降序，空值排最后
                ID DESC               -- VAL为空时按ID降序
        ) AS rn
    FROM your_table
) t
WHERE rn = 1;

• 逻辑说明：
  • PARTITION BY GROUPCOL 按分组字段拆分数据
  • ORDER BY VAL DESC NULLS LAST 确保非空的VAL优先取最大值，空值后置
  • ORDER BY ID DESC 当VAL为空时，取组内ID最大的记录
  • 筛选每组第一条记录（rn=1）就是目标结果

方式2：兼容低版本MySQL（无窗口函数）

如果是MySQL 5.x这类不支持窗口函数的版本，可用子查询关联实现：

SELECT t1.ID, t1.VAL, t1.GROUPCOL
FROM your_table t1
JOIN (
    SELECT 
        GROUPCOL,
        MAX(CASE WHEN VAL IS NOT NULL THEN VAL ELSE -1 END) AS max_val,  -- 用-1替代空值便于取最大
        MAX(ID) AS max_id
    FROM your_table
    GROUP BY GROUPCOL
) t2 ON t1.GROUPCOL = t2.GROUPCOL
WHERE 
    (t1.VAL = t2.max_val AND t2.max_val != -1)
    OR 
    (t1.VAL IS NULL AND t1.ID = t2.max_id);

• 逻辑说明：
  • 子查询先计算每组的最大VAL（空值用-1替代）和最大ID
  • 主查询关联后，筛选出对应最大VAL的记录，或VAL为空且ID为组内最大的记录

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