SQL GROUP BY分组后获取指定用户首行值及组内最大值的实现

表结构 #

需求 #

对每个GROUP_ID分组，完成两项计算：

• 取组内USER_ID为101、IX_IN_GROUP最小的行对应的MEASUREMENT值，记为X
• 取组内所有行的MEASUREMENT最大值，记为Y

期望输出：

解决方案 #

方法一：窗口函数（推荐，高效简洁） #

利用窗口函数一次性完成两个值的计算，无需多表关联：

SELECT DISTINCT
    GROUP_ID,
    -- 筛选USER_ID=101的行，取分组内按IX_IN_GROUP排序的第一个值
    FIRST_VALUE(CASE WHEN USER_ID = 101 THEN MEASUREMENT END) OVER (
        PARTITION BY GROUP_ID 
        ORDER BY IX_IN_GROUP 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS X,
    -- 直接取分组内的MEASUREMENT最大值
    MAX(MEASUREMENT) OVER (PARTITION BY GROUP_ID) AS Y
FROM table1
ORDER BY GROUP_ID;

注：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING确保窗口包含分组内所有行，避免因排序导致窗口范围限制；DISTINCT用于去重，保留每个分组唯一一行结果。

方法二：子查询关联（兼容老版本数据库） #

如果数据库不支持窗口函数，可以通过子查询关联的方式实现：

SELECT
    t1.GROUP_ID,
    -- 子查询定位每个分组内USER_ID=101且IX_IN_GROUP最小的行
    (SELECT MEASUREMENT 
     FROM table1 t2 
     WHERE t2.GROUP_ID = t1.GROUP_ID 
       AND t2.USER_ID = 101 
     ORDER BY t2.IX_IN_GROUP ASC 
     LIMIT 1) AS X,
    MAX(t1.MEASUREMENT) AS Y
FROM table1 t1
GROUP BY t1.GROUP_ID
ORDER BY t1.GROUP_ID;

主查询按GROUP_ID分组计算最大值Y，子查询针对每个分组获取符合条件的X值。

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