如何按test_run_id分组并筛选每组exe_length最小的记录

原始数据表格 #

需求说明 #

针对每个test_run_id，筛选出对应exe_length最小的记录的test_run_id、suite_id以及message字段。

示例：对于test_run_id=4201的分组，需获取exe_length最小值11对应的message（即Hub Status does1）。

单条硬编码查询SQL：

select  message
from log_message_view_uat
where test_run_id= 4201 and test_id=32 and suite_id=39 and exe_length = (select min(exe_length) from log_message_view_uat
where test_run_id= 4201 and test_id=32 and suite_id=39)

批量查询实现方案 #

方法1：窗口函数（推荐） #

利用ROW_NUMBER()按test_run_id分组，每组内按exe_length升序排序，取排序后第一条记录。若需保留同一test_run_id下所有最小长度的记录，将ROW_NUMBER()替换为RANK()或DENSE_RANK()即可。

SELECT test_run_id, suite_id, message
FROM (
    SELECT 
        test_run_id, 
        suite_id, 
        message,
        ROW_NUMBER() OVER (PARTITION BY test_run_id ORDER BY exe_length ASC) AS rn
    FROM log_message_view_uat
) t
WHERE rn = 1;

方法2：关联子查询 #

先计算每个test_run_id的最小exe_length，再关联原表筛选匹配记录，可保留所有最小长度的记录。

SELECT l.test_run_id, l.suite_id, l.message
FROM log_message_view_uat l
JOIN (
    SELECT test_run_id, MIN(exe_length) AS min_exe_length
    FROM log_message_view_uat
    GROUP BY test_run_id
) m ON l.test_run_id = m.test_run_id AND l.exe_length = m.min_exe_length;

方法3：WHERE子查询 #

直接判断当前记录的exe_length是否为对应test_run_id的最小值，逻辑简洁，同样保留所有最小长度的记录。

SELECT test_run_id, suite_id, message
FROM log_message_view_uat l
WHERE exe_length = (
    SELECT MIN(exe_length)
    FROM log_message_view_uat
    WHERE test_run_id = l.test_run_id
);

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