基于第一个表的值的联接表的最大值

2024-05-21 • 问答

我有两张桌子。

第一个保存作业详细信息，第二个保存这些作业运行的历史记录。第一个还包含每个客户的作业周期，这是在为同一客户运行下一个作业之前等待的最短时间。时间比较需要发生在第二个表的 started_on 字段上。

我需要找出接下来要运行的作业 ID。

架构

job_details 表

CREATE TABLE `job_details` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,`customer_id` varchar(128) NOT NULL,`period_in_minutes` int(11) unsigned NOT NULL,`status` enum('actIVE','INactIVE','DELETED') DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

job_run_history 表

CREATE TABLE `job_run_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,`job_id` int(10) unsigned NOT NULL,`started_on` timestamp NULL DEFAULT NULL,`status` enum('STREAMING','STREAMED','UPLOADING','UPLOADED','NO_RECORDS','FAILED') DEFAULT NULL,PRIMARY KEY (`id`),KEY `fk_job_id` (`job_id`),CONSTRAINT `fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job_details` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

job_details 表的示例数据：

INSERT INTO `job_details` (`id`,`customer_id`,`period_in_minutes`,`status`)
VALUES
    (1,'cust1',1,'actIVE'),(2,'cust2',(3,'cust3',2,'actIVE');

job_run_history 表的示例数据：

INSERT INTO `job_run_history`(`job_id`,`started_on`,'2021-07-01 14:38:00','UPLOADED'),'2021-07-01 14:37:55','UPLOADED');

预期输出（在 2021-07-01 14:38:56 运行时）：

id
2,3

1. id => 1 没有被选中，因为最后一个作业在过去 1 分钟内开始
1. id => 2 DID 被选中，因为最后一个作业开始时间超过 1 分钟
1. id => 3 DID 被选中，因为它没有运行历史

我已经尝试过这个，但这不能与 start_time 的最大值进行比较，因此，不起作用：

select jd.id,max(jrh.started_on) from job_details jd 
left join job_run_history jrh on jrh.job_id=jd.id 
where 
    jd.status='actIVE' 
    and (jrh.status is null or jrh.status not in ('STREAMING','UPLOADING')) 
    and (jrh.`started_on` is null or jrh.`started_on` < date_sub(now(),interval jd.`period_in_minutes`*60 second))
group by jd.id;

MySql 版本：5.7.34

请问有什么帮助吗？提前致谢..

-- the subquery for the rows which have matched ones in 2nd table SELECT t1.id FROM job_details t1 JOIN job_run_history t2 ON t1.id = t2.job_id WHERE t1.status = 'ACTIVE' AND t2.status not in ('STREAMING','STREAMED','UPLOADING') AND CURRENT_TIMESTAMP - INTERVAL t1.period_in_minutes MINUTE > t2.started_on UNION ALL -- the subquery for the rows which have no matched ones in 2nd table SELECT id FROM job_details t1 WHERE NOT EXISTS ( SELECT NULL FROM job_run_history t2 WHERE t1.id = t2.job_id ) AND status = 'ACTIVE';

select jd.id from job_details jd left join job_run_history jrh on jd.id= jrh.job_id where jd.status = 'ACTIVE' group by jd.id having max(jrh.started_on) < current_timestamp - interval max(jd.period_in_minutes) minute or max(jrh.id) is null

基于第一个表的值的联接表的最大值

iCMS 回答：基于第一个表的值的联接表的最大值

大家都在问