如何按列值筛选并提取runs表中各组最新记录（SQL需求）

原数据表结构与数据 #

runs表的结构和数据如下：

需求说明 #

需在以下筛选条件下，提取每个input与type组合对应的最新记录：

• job_id = 61
• type in ("Pre", "Post")
• input in ("show name", "show dob")

预期查询结果 #

解决方案 #

使用窗口函数ROW_NUMBER()可以高效实现需求，具体SQL语句如下：

SELECT id, job_id, command_id, type, input, created_at
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY input, type ORDER BY created_at DESC) AS rn
    FROM runs
    WHERE 
        job_id = 61
        AND type IN ('Pre', 'Post')
        AND input IN ('show name', 'show dob')
) t
WHERE rn = 1;

思路说明 #

1. 内层子查询：先筛选出符合条件的记录，再通过PARTITION BY input, type将数据按input和type的组合分组，每组内按created_at降序排序，用ROW_NUMBER()给每组内的记录编号，最新的记录编号为1。
2. 外层查询：直接筛选出编号为1的记录，即为每个组合的最新记录。

如果存在多条记录created_at完全相同的情况，若需保留所有这些最新记录，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

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