如何用SQL按条件获取每组最新记录与原始审批记录？

需求说明 #

从指定数据表中按条件获取每组的最新记录及原始记录。

数据表特性 #

• 每组可包含多条记录
• 同一基础item对应的item_name若为变更版本，括号内数字代表变更迭代次数（例如DE-1234(2)是DE-1234的第2次变更）
• 记录状态分为*Draft（草稿）和Approved（已审批）*两类

提取规则 #

1. 提取每组最新的记录（按日期排序取最新）
2. 若该最新记录状态不为Approved（即处于变更流程中），则同步提取该组内最新的已审批记录作为原始记录；若最新记录状态为Approved，原始记录字段填NULL

示例数据表 #

期望输出 #

SQL解决方案 #

思路 #

1. 为每组记录按日期降序排序，标记出每组的最新记录
2. 筛选出所有已审批记录，按分组和日期降序排序，标记出每组最新的已审批记录
3. 关联两个结果集，根据最新记录的状态决定是否填充原始记录字段

代码实现 #

WITH latest_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY 分组 ORDER BY 日期 DESC) AS rn_latest
    FROM your_table_name
),
latest_approved AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY 分组 ORDER BY 日期 DESC) AS rn_approved
    FROM your_table_name
    WHERE status = 'Approved'
)
SELECT 
    lr.分组,
    lr.日期,
    lr.item_id,
    lr.item_name,
    lr.status,
    lr.价格,
    lr.库存,
    CASE WHEN lr.status != 'Approved' THEN la.item_id ELSE NULL END AS original_item_id,
    CASE WHEN lr.status != 'Approved' THEN la.item_name ELSE NULL END AS original_item_name,
    CASE WHEN lr.status != 'Approved' THEN la.status ELSE NULL END AS original_status,
    CASE WHEN lr.status != 'Approved' THEN la.价格 ELSE NULL END AS original_price,
    CASE WHEN lr.status != 'Approved' THEN la.库存 ELSE NULL END AS original_stock
FROM latest_records lr
LEFT JOIN latest_approved la ON lr.分组 = la.分组 AND la.rn_approved = 1
WHERE lr.rn_latest = 1
ORDER BY lr.分组;

代码说明 #

• latest_records CTE：通过ROW_NUMBER()函数按分组和日期降序排序，标记每组的最新记录（rn_latest=1）
• latest_approved CTE：仅筛选已审批记录，同样用ROW_NUMBER()标记每组最新的已审批记录（rn_approved=1）
• 主查询：关联两个CTE，取每组最新记录，根据其状态判断是否填充原始记录字段；若最新记录为已审批状态，原始字段统一填NULL

注意：将代码中的your_table_name替换为实际的数据表名称。

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