如何使用CASE表达式或其他方式实现Report_Table特定查询需求

需求回顾 #

针对Report_Table的数据，需实现：

• 当记录状态为Success时，返回对应报表（按Report_CORE_NAME关联）的最新Success完成日期
• 当记录状态为Error时，同时返回对应报表的最新Success完成日期和当前Error记录的完成日期

方案一：子查询+UNION ALL实现 #

SELECT
    rt.Report_DEFINITION_NAME,
    rt.Report_CORE_NAME,
    rt.COMPLETION_STATUS,
    rt.COMPLETION_DATE AS Error_COMPLETION_DATE,
    latest_success.LATEST_SUCCESS_DATE
FROM
    Report_Table rt
LEFT JOIN (
    SELECT
        Report_CORE_NAME,
        MAX(COMPLETION_DATE) AS LATEST_SUCCESS_DATE
    FROM
        Report_Table
    WHERE
        COMPLETION_STATUS = 'Success'
    GROUP BY
        Report_CORE_NAME
) latest_success ON rt.Report_CORE_NAME = latest_success.Report_CORE_NAME
WHERE
    rt.COMPLETION_STATUS = 'Error'
UNION ALL
SELECT
    rt.Report_DEFINITION_NAME,
    rt.Report_CORE_NAME,
    rt.COMPLETION_STATUS,
    NULL AS Error_COMPLETION_DATE,
    latest_success.LATEST_SUCCESS_DATE
FROM
    Report_Table rt
INNER JOIN (
    SELECT
        Report_CORE_NAME,
        MAX(COMPLETION_DATE) AS LATEST_SUCCESS_DATE
    FROM
        Report_Table
    WHERE
        COMPLETION_STATUS = 'Success'
    GROUP BY
        Report_CORE_NAME
) latest_success ON rt.Report_CORE_NAME = latest_success.Report_CORE_NAME
WHERE
    rt.COMPLETION_STATUS = 'Success'
    AND rt.COMPLETION_DATE = latest_success.LATEST_SUCCESS_DATE;

逻辑说明

1. 子查询latest_success先计算每个Report_CORE_NAME对应的最新Success完成日期
2. 第一部分查询所有Error状态记录，左连接子查询结果，返回Error日期和最新Success日期
3. 第二部分筛选每个报表最新的Success记录，返回最新Success日期，Error日期列置为NULL
4. 通过UNION ALL合并两个结果集，覆盖所有符合需求的记录

方案二：窗口函数实现（更简洁） #

WITH report_stats AS (
    SELECT
        *,
        MAX(CASE WHEN COMPLETION_STATUS = 'Success' THEN COMPLETION_DATE END) 
            OVER (PARTITION BY Report_CORE_NAME) AS latest_success_date,
        ROW_NUMBER() OVER (PARTITION BY Report_CORE_NAME, COMPLETION_STATUS 
                           ORDER BY COMPLETION_DATE DESC) AS rn
    FROM Report_Table
)
SELECT
    Report_DEFINITION_NAME,
    Report_CORE_NAME,
    COMPLETION_STATUS,
    CASE WHEN COMPLETION_STATUS = 'Error' THEN COMPLETION_DATE END AS error_completion_date,
    latest_success_date
FROM report_stats
WHERE 
    (COMPLETION_STATUS = 'Success' AND rn = 1)
    OR COMPLETION_STATUS = 'Error';

逻辑说明

1. CTEreport_stats中：
   • 通过MAX() OVER (PARTITION BY Report_CORE_NAME)计算每个报表的最新Success日期
   • 通过ROW_NUMBER()给每个报表同状态的记录按日期倒序编号
2. 过滤条件保留：所有Error状态记录，以及每个报表最新的Success状态记录（rn=1）
3. 用CASE语句控制仅在Error状态时显示当前记录的日期

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