SQL Server查询：获取进行中批次及关联已完成任务

问题分析 #

要实现需求需分两步处理：

1. 锁定当前处于进行中的批次：每个批次在TableA中存在多条状态记录，需以最新时间（dt_utc）对应的batch_status为准，筛选出状态为0的批次。
2. 关联TableB提取已完成任务：每个任务在TableB中可能有多条状态记录，需取最新时间（task_description字段存储的时间）对应的task_status为1的任务。

修正后的SQL语句 #

SELECT 
    a.batch_identifier,
    b.task_identifier,
    b.task_description,
    b.task_status
FROM (
    -- 子查询1：获取每个批次的最新状态，筛选进行中的批次
    SELECT 
        batch_identifier,
        batch_status
    FROM (
        SELECT 
            batch_identifier,
            batch_status,
            ROW_NUMBER() OVER (PARTITION BY batch_identifier ORDER BY dt_utc DESC) AS rn
        FROM dbo.TableA
    ) t
    WHERE rn = 1 AND batch_status = 0
) a
INNER JOIN (
    -- 子查询2：获取每个任务的最新状态，筛选已完成的任务
    SELECT 
        batch_identifier,
        task_identifier,
        task_description,
        task_status
    FROM (
        SELECT 
            batch_identifier,
            task_identifier,
            task_description,
            task_status,
            ROW_NUMBER() OVER (PARTITION BY batch_identifier, task_identifier ORDER BY CAST(task_description AS DATETIME) DESC) AS rn
        FROM dbo.TableB
    ) t
    WHERE rn = 1 AND task_status = 1
) b ON a.batch_identifier = b.batch_identifier

原语句问题说明 #

你之前的SQL存在两处核心问题：

• 使用MAX(CAST(batch_status as int))判断批次状态逻辑错误：MAX仅取状态值的最大值，无法反映批次的最新状态（比如批次先完成后重新进入进行中的场景，MAX会错误返回1）。
• 缺少关联TableB获取已完成任务的逻辑，无法覆盖完整需求。

索引优化提示 #

已知batch_identifier和task_identifier为索引，上述SQL中的窗口函数PARTITION BY会自动利用这些索引提升查询效率，无需额外调整索引结构。

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