在mysql查询中计算日期和持续时间 [英] Compute dates and durations in mysql query
问题描述
我有一个示例表(table_name:track_task),如下所示:
I have a sample table (table_name: track_task) like below:
task_id stage log_date
----------------------------------------
3 1 2011-06-01 08:36:21
9 1 2011-06-03 12:35:47
3 2 2011-06-05 14:25:42
21 1 2011-06-11 13:03:34
9 2 2011-06-11 15:25:57
3 3 2011-06-12 10:16:09
21 2 2011-06-15 15:30:29
3 4 2011-06-22 15:34:33
21 3 2011-06-23 12:53:49
9 4 2011-06-25 16:25:08
当应用程序代码中的某些操作使任务阶段进展时,以上数据将自动填充.阶段从1到4.但是,由于某种逻辑,任务可能会跳过阶段3.但是所有任务都将在阶段4结束.可能的任务路径如下:
The data above is automatically populated when a task stage is progressed by some action in the application code. The stages run from 1 to 4. However, due to some logic, a task may skip stage 3. But all tasks end at stage 4. Probable task paths are like so:
(1,2,3,4) / (1,2,4) - Completed tasks
(1,2,3) / (1,2) - In progress tasks
我需要查询和检索一个报告,该报告显示任务在给定时间的每个阶段需要多长时间(以天为单位).到目前为止,我已经提出了以下查询:
I need to query and retrieve a report that shows how long (in days) a task takes in each stage at a given time. I have come up with the following query so far:
SELECT z.task_id, a.log_date begin_date, d.log_date end_date,
DATEDIFF( b.log_date, a.log_date ) step1_days,
DATEDIFF( c.log_date, b.log_date ) step2_days,
DATEDIFF( d.log_date, c.log_date ) step3_days,
DATEDIFF( d.log_date, a.log_date ) cycle_days
FROM track_task z
LEFT JOIN track_task a ON ( z.task_id = a.task_id AND a.staging_id =1 )
LEFT JOIN track_task b ON ( z.task_id = b.task_id AND b.staging_id =2 )
LEFT JOIN track_task c ON ( z.task_id = c.task_id AND c.staging_id =3 )
LEFT JOIN track_task d ON ( z.task_id = d.task_id AND d.staging_id =4 )
GROUP BY z.oppty_id
得出如下结果集:
task_id begin_date end_date step1_days step2_days step3_days cycle_days
-------------------------------------------------------------------------------
3 2011-06-01 2011-06-22 4 7 10 21
9 2011-06-03 2011-06-25 8 NULL NULL 22
21 2011-06-11 NULL 4 8 NULL NULL
这是解决问题的好方法还是更好的方法?如何将NULL值报告为零?我如何才能检索到仍在进行中的任务的end_date?
Is this a good way to go about it or there is a better way? How can I have the NULL values reported as zero? How can I retrieve then end_date for a task that is still in progress?
推荐答案
那是关于我将如何做这样的事情,假设每个task
只会使用一个stage
的实例(如果没有的话) ,您需要做更多的工作,包括定义需求和编写查询).
That looks about How I would do something like this, assuming there's only going to ever be one instance of a stage
used per task
(if not, you need more work, both in defining your requirements, and writing the query).
要使null
显示为0
,请使用COALESCE()
功能:
To make null
appear as 0
, use the COALESCE()
function:
SELECT z.task_id, COALESCE(DATEDIFF(b.log_date, a.log_date), 0) as step1_days
这篇关于在mysql查询中计算日期和持续时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!