按ID分组获取taskA和taskB的最新记录（采用Pivot/条件聚合）

需求说明 #

按ID分组，依据assign_date字段获取每个ID对应的taskA和taskB的最新记录，将对应task的处理人（assignee）转为列，全程避免多表连接，仅通过pivot或条件聚合实现。

源数据格式 #

期望结果格式 #

实现方案 #

方案1：通用SQL条件聚合（支持多数数据库） #

通过窗口函数筛选每个ID+task的最新记录，再用条件聚合转列：

SELECT
    ID,
    MAX(CASE WHEN task = 'taskA' THEN assignee END) AS assignee_taskA,
    MAX(CASE WHEN task = 'taskB' THEN assignee END) AS assignee_taskB
FROM (
    SELECT
        ID,
        task,
        assignee,
        -- 按ID+task分组，最新的记录行号标记为1
        ROW_NUMBER() OVER (PARTITION BY ID, task ORDER BY assign_date DESC) AS rn
    FROM your_table
    WHERE task IN ('taskA', 'taskB') -- 仅筛选目标任务，提升查询效率
) t
WHERE rn = 1 -- 只保留每个ID+task的最新记录
GROUP BY ID;

方案2：使用PIVOT语法（适用于Oracle、SQL Server等支持PIVOT的数据库） #

先筛选最新记录，再通过PIVOT直接转列：

SELECT
    ID,
    assignee_taskA,
    assignee_taskB
FROM (
    SELECT
        ID,
        task,
        assignee,
        ROW_NUMBER() OVER (PARTITION BY ID, task ORDER BY assign_date DESC) AS rn
    FROM your_table
    WHERE task IN ('taskA', 'taskB')
) t
PIVOT (
    MAX(assignee)
    FOR task IN ('taskA' AS assignee_taskA, 'taskB' AS assignee_taskB)
) p
WHERE rn = 1;

说明 #

两种方案均无需多表连接：

• 子查询通过ROW_NUMBER()窗口函数为每个ID下的同类型任务按时间倒序标记，确保仅保留最新记录
• 外层通过条件聚合或PIVOT语法，将横向的task记录转为纵向的列，直接得到目标结果

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