在mysql查询中计算日期和持续时间

Question

我有一个示例表（table_name：track_task），如下所示：

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结束。可能的任务路径如下所示：

(1,2,3,4) / (1,2,4) - Completed tasks
(1,2,3) / (1,2)     - In progress tasks

我需要查询和检索一个报告，该报告显示任务在给定时间在每个阶段需要多长时间（以天为单位）。到目前为止，我已经提出了以下查询：

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？

Answer 1

这对我会怎么做这样的事情，假设有只打算永远是一个的一个实例看起来stage每使用task（如果没有，则需要更多的工作，无论是在确定您的要求，并编写查询）。

要使null显示为0，请使用以下COALESCE()功能：

SELECT z.task_id, COALESCE(DATEDIFF(b.log_date, a.log_date), 0) as step1_days