基于job_detail表生成事件状态统计的SQL查询请求

现有一张名为job_detail的表，字段及示例记录如下：

Evnt_name   event_job_nm   status   start_time   end_time
---------   ------------   ------   ----------   ----------
evnt_abc    evnt_abc_job1   C       02/04/2023   02/04/2023
evnt_abc    evnt_abc_job2   C       02/04/2023   02/04/2023
evnt_abc    evnt_abc_job3   C       02/04/2023   02/04/2023
evnt_abc    evnt_abc_job4   C       02/04/2023   02/04/2023
evnt_jkl    evnt_jkl_job1   C       02/04/2023   02/04/2023
evnt_jkl    evnt_jkl_job2   C       02/04/2023   02/04/2023
evnt_jkl    evnt_jkl_job3   C       02/04/2023   02/04/2023
evnt_jkl    evnt_jkl_job4   F       02/04/2023   02/04/2023
.............
evnt_xyz    evnt_xyz_job1   C       02/04/2023   02/04/2023
evnt_xyz    evnt_xyz_job2   F       02/04/2023   02/04/2023
evnt_xyz    evnt_xyz_job3   N       02/04/2023   02/04/2023
evnt_xyz    evnt_xyz_job4   N       02/04/2023   02/04/2023

状态含义：

• C：已完成
• F：失败
• N：未启动

需求 #

每个事件对应4条任务记录，需编写SQL实现：

• 若某事件下所有任务均成功（状态为C），则事件状态为C；
• 若该事件下存在任意失败任务（状态为F），则事件状态为F；

输出格式要求：

Evnt_name    evnt_status
---------    -----------
evnt_abc       C
evnt_jkl       F
evnt_xyz       F

解决方案 #

以下提供两种常用的SQL写法，均能满足需求：

写法1：通过条件计数判断是否存在失败任务

SELECT 
    Evnt_name,
    CASE 
        WHEN SUM(CASE WHEN status = 'F' THEN 1 ELSE 0 END) > 0 THEN 'F'
        ELSE 'C'
    END AS evnt_status
FROM job_detail
GROUP BY Evnt_name;

写法2：利用字符排序优先级简化逻辑

由于字符排序中'F' > 'C' > 'N'，直接取每个事件的最大状态值即可得到结果：

SELECT 
    Evnt_name,
    MAX(status) AS evnt_status
FROM job_detail
GROUP BY Evnt_name;

写法2更为简洁，但若后续状态编码规则发生变化，需重新确认字符排序逻辑是否适配。

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