SQL嵌套查询：获取代理下各作业最新运行记录的实现方法

需要获取的结果包含以下4列，预期返回每个代理下每个作业最新一次的运行记录，样例结果如下：

业务规则：单个代理上的多个作业在单日/单月周期内会多次运行，需要按Last Run Timestamp倒序取每个作业的Top1运行记录。

当前执行以下SQL时返回数百万条重复作业行，无法得到预期结果：

select Agent_Name, Job_Name, Client_id, Last_Run_Timestamp
from table1
  join table2 on table.idnr = table1.idnr
where client_id = 10
  and exists (select job_name from listofjobs)
order by Agent_Name, timestamp desc

• 原SQL未做分组取最新值的逻辑，直接关联两表会返回所有历史运行记录，数据量和作业运行总次数一致，所以会出现百万级冗余数据
• exists子句缺少和主表的关联条件，只要listofjobs表有数据就会返回真，完全起不到过滤指定作业的作用
• 全局order by仅做结果排序，不会自动按「代理+作业」维度分组取第一条

方案1：窗口函数写法（推荐，支持MySQL8.0+、PostgreSQL、SQL Server、Oracle等主流数据库） #

通过ROW_NUMBER()窗口函数按代理、作业、客户端ID分区，按运行时间倒序打排名，取排名为1的记录即可，逻辑简洁性能好：

SELECT 
    Agent_Name, 
    Job_Name, 
    Client_id, 
    Last_Run_Timestamp
FROM (
    SELECT 
        t1.Agent_Name, 
        t1.Job_Name, 
        t1.Client_id, 
        t2.Last_Run_Timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY t1.Agent_Name, t1.Job_Name, t1.Client_id 
            ORDER BY t2.Last_Run_Timestamp DESC
        ) AS rn
    FROM table1 t1
    JOIN table2 t2 ON t1.idnr = t2.idnr
    WHERE t1.Client_id = 10
        -- 修正原exists逻辑，关联作业表做过滤
        AND EXISTS (
            SELECT 1 
            FROM listofjobs loj 
            WHERE loj.job_name = t1.Job_Name
        )
) tmp
WHERE rn = 1
ORDER BY Agent_Name, Job_Name;

方案2：关联最大值写法（兼容MySQL5.x等不支持窗口函数的老版本数据库） #

先子查询分组算出每个代理下每个作业的最新运行时间，再关联原表拿到对应记录：

SELECT 
    t1.Agent_Name, 
    t1.Job_Name, 
    t1.Client_id, 
    t2.Last_Run_Timestamp
FROM table1 t1
JOIN table2 t2 ON t1.idnr = t2.idnr
INNER JOIN (
    SELECT 
        t1_inner.Agent_Name,
        t1_inner.Job_Name,
        t1_inner.Client_id,
        MAX(t2_inner.Last_Run_Timestamp) AS latest_run_time
    FROM table1 t1_inner
    JOIN table2 t2_inner ON t1_inner.idnr = t2_inner.idnr
    WHERE t1_inner.Client_id = 10
        AND EXISTS (
            SELECT 1 
            FROM listofjobs loj 
            WHERE loj.job_name = t1_inner.Job_Name
        )
    GROUP BY t1_inner.Agent_Name, t1_inner.Job_Name, t1_inner.Client_id
) latest_job
    ON t1.Agent_Name = latest_job.Agent_Name
    AND t1.Job_Name = latest_job.Job_Name
    AND t1.Client_id = latest_job.Client_id
    AND t2.Last_Run_Timestamp = latest_job.latest_run_time
WHERE t1.Client_id = 10
    AND EXISTS (
        SELECT 1 
        FROM listofjobs loj 
        WHERE loj.job_name = t1.Job_Name
    )
ORDER BY t1.Agent_Name, t1.Job_Name;

• 如果同一个代理下的同一个作业存在两条时间戳完全一致的运行记录，可将窗口函数的ROW_NUMBER()替换为RANK()避免漏数；关联最大值写法会默认返回这两条时间相同的记录，可根据业务需求选择
• 如果不需要对照listofjobs过滤作业范围，可直接删除SQL中对应的EXISTS子句

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