同一张表中两个记录之间的日期差 [英] Date difference between two records in same table
问题描述
我有一个具有以下记录的表(job_logs
):
id
,job_id
,user_id
,status
,created_at
,job_type
.
I have a table (job_logs
) with the following records:
id
, job_id
, user_id
, status
, created_at
, job_type
.
每次作业开始运行记录时,都会用status='started'
记录在job_log
表中.当作业完成运行时,使用status='completed'
将另一条记录添加到表中.
Each time a job starts to run a record is written in the job_log
table with status='started'
. When a job finish running another record is added to the table with status='completed'
.
两个记录都具有相同的user_id
,job_type
和 job_id (由运行作业的进程确定-这两个记录是唯一的).
Both records has the same user_id
, job_type
and job_id (which is determined by the process running the job - unique to these 2 records).
我想要一个查询,该查询将返回表中的所有这些记录对(按ID desc排序),但棘手的部分是我想将作业运行所花费的时间添加到已完成"状态的记录中(completed.created_at - started.created_at
).
I want a query that will return all these records pairs in the table (ordered by id desc) but the tricky part is that I want to add to the record with the 'completed' status the time it took the job to run (completed.created_at - started.created_at
).
我该怎么做?
推荐答案
SELECT j1.job_id AS job_id, (j2.created_at - j1.created_at) AS time_run
FROM job_logs j1 INNER JOIN job_logs j2 ON (j1.job_id = j2.job_id)
WHERE j1.status = 'started' AND j2.status = 'completed'
这篇关于同一张表中两个记录之间的日期差的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!