我必须遵循图1下面的查询，该查询可以工作，但是我想做的是获取第4行和第5行之间的timediff，然后获取第5行和第6行之间的差异。
目的是获得类似于图2的内容。理想情况下，我想在查询中执行此操作，而不必遍历数据库中的每一行然后返回PHP。
提前致谢。
图。1
select * from ModuleFlowModuleStatus where ModuleCode = "LW2205" ORDER BY UpdatedOn;
| ID | Module | MoudleStatus | UpdatedOn |
| 4 | LW2205 | Draft exam received | 2017-10-18 12:41:12 |
| 5 | LW2205 | Draft exam received | 2017-10-18 12:41:23 |
| 7 | LW2205 | Draft exam received | 2017-10-20 15:06:46 |
| 275 | LW2205 | Exam approved by Dean | 2017-11-14 16:39:28 |
| 288 | LW2205 | Final exam sign off by | 2017-11-21 12:28:59 |
| 295 | LW2205 | Exam sent to SREO (Stud | 2017-11-23 09:53:30 |
+-----+--------+-------------------------+---------------------+
图。 2预期结果
| ID | Module | MoudleStatus | UpdatedOn | Diff(days)
| 4 | LW2205 | Draft exam received | 2017-10-18 12:41:12 | 0
| 5 | LW2205 | Draft exam received | 2017-10-18 12:41:23 | 0
| 7 | LW2205 | Draft exam received | 2017-10-20 15:06:46 | 2
| 275 | LW2205 | Exam approved by Dean | 2017-11-14 16:39:28 | 24
| 288 | LW2205 | Final exam sign off by | 2017-11-21 12:28:59 | 7
| 295 | LW2205 | Exam sent to SREO (Stud | 2017-11-23 09:53:30 | 3
+-----+--------+-------------------------+---------------------+
最佳答案
此SQL代码适用于您应用于图1中的表：
SELECT
t1.ID,
t1.Module,
t1.MoudleStatus,
t1.UpdatedOn,
IFNULL(DATEDIFF(
t1.UpdatedOn,
(SELECT MAX(t2.UpdatedOn) FROM ModuleFlowModuleStatus AS t2 WHERE t2.id < t1.id)
), 0) AS `Diff(Days)`
FROM
ModuleFlowModuleStatus AS t1;
该代码可以进行一些优化/改进，但是对我有用。为了简化我的代码，我省略了WHERE和ORDER BY子句，只需要再次添加它们。让我知道是否有帮助。哦，默认情况下，MySQL DATEDIFF函数以天为单位返回该值，我认为这正是您想要的。