JOB_LOGS表未完成作业查询优化：解决重复行问题

表结构 #

CREATE TABLE `JOB_LOGS` 
(
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `created_at` datetime DEFAULT NULL,
    `job_id` varchar(1024) DEFAULT NULL,
    `event` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `IDX_QRTZ_EL_EVENT` (`event`),
    KEY `IDX_QRTZ_EL_JOB_ID` (`job_id`)
);

事件字段可选值 #

('ADDED', 'INPROGRESS', 'SUCCESS', 'FAILED')

示例数据集 #

• 作业1：

1. id=1,created_at="2022-01-01 00:00:00",job_id=Job1,event=ADDED
2. id=2,created_at="2022-01-01 00:01:00",job_id=Job1,event=INPROGRESS
3. id=3,created_at="2022-01-01 00:01:50",job_id=Job1,event=SUCCESS

• 作业2：

1. id=4,created_at="2022-01-01 00:00:00",job_id=Job2,event=ADDED
2. id=5,created_at="2022-01-01 00:01:01",job_id=Job2,event=INPROGRESS
3. id=6,created_at="2022-01-01 00:01:53",job_id=Job2,event=FAILED

• 作业3：

1. id=7,created_at="2022-01-01 00:00:00",job_id=Job3,event=ADDED

• 作业4：

1. id=8,created_at="2022-01-01 00:00:00",job_id=Job3,event=ADDED   
2. id=9,created_at="2022-01-01 00:00:00",job_id=Job3,event=INPROGRESS

查询需求 #

1. 获取尚未成功（SUCCESS）或失败（FAILED）的作业日志
2. 获取近24小时内仅处于ADDED状态、未进入其他状态的作业日志

原查询问题 #

原查询用左连接时未做精准逻辑过滤，同一job_id的多条日志被重复关联，导致返回大量重复行，不符合预期。

优化后的SQL查询 #

需求1：获取尚未成功或失败的作业日志 #

提供两种高效写法，均能避免重复行：

写法1：NOT EXISTS子查询

SELECT DISTINCT b1.*
FROM JOB_LOGS b1
WHERE NOT EXISTS (
    SELECT 1 
    FROM JOB_LOGS b2 
    WHERE b2.job_id = b1.job_id 
      AND b2.event IN ('SUCCESS', 'FAILED')
);

逻辑：检查当前job_id是否存在成功/失败记录，无则保留该日志行，DISTINCT确保无重复。

写法2：分组过滤后关联

SELECT b1.*
FROM JOB_LOGS b1
JOIN (
    SELECT job_id
    FROM JOB_LOGS
    GROUP BY job_id
    HAVING SUM(CASE WHEN event IN ('SUCCESS', 'FAILED') THEN 1 ELSE 0 END) = 0
) b2 ON b1.job_id = b2.job_id;

逻辑：先分组找出无成功/失败记录的job_id，再关联获取这些job_id的所有日志，天然无重复。

需求2：获取近24小时内仅处于ADDED状态的作业日志 #

两种可行写法：

写法1：NOT EXISTS子查询

SELECT DISTINCT b1.*
FROM JOB_LOGS b1
WHERE b1.event = 'ADDED'
  AND b1.created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
  AND NOT EXISTS (
      SELECT 1 
      FROM JOB_LOGS b2 
      WHERE b2.job_id = b1.job_id 
        AND b2.event IN ('INPROGRESS', 'SUCCESS', 'FAILED')
);

逻辑：筛选近24小时的ADDED日志，同时确保该job_id无其他状态记录。

写法2：分组过滤

SELECT b1.*
FROM JOB_LOGS b1
JOIN (
    SELECT job_id
    FROM JOB_LOGS
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
    GROUP BY job_id
    HAVING COUNT(DISTINCT event) = 1 
       AND MAX(event) = 'ADDED'
) b2 ON b1.job_id = b2.job_id
WHERE b1.event = 'ADDED';

逻辑：先找出近24小时内只有ADDED一种状态的job_id，再关联获取对应的ADDED日志。

预期结果验证 #

• 需求1预期返回：

id=7,created_at="2022-01-01 00:00:00",job_id=Job3,event=ADDED
id=8,created_at="2022-01-01 00:00:00",job_id=Job3,event=ADDED
id=9,created_at="2022-01-01 00:00:00",job_id=Job3,event=INPROGRESS

（注：原示例中作业4的id=8属于Job3的ADDED日志，符合未完成条件，应被包含）

• 需求2预期返回：

id=7,created_at="2022-01-01 00:00:00",job_id=Job3,event=ADDED

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