MySQL中如何基于任务标识计算状态变更的时间间隔？

我有一张包含2万余行数据的HISTORY日志表，用来记录任务的状态变更信息。需求是计算同一任务（P_KEY相同）每次状态（fieldJ="status"）变更与上一次变更的时间间隔，但任务存在多次状态变更，没法只通过过滤起止状态来实现。

表结构查询语句 #

Select P_ID,P_KEY,AUTHOR,Created,fieldJ,fromString,toString from HISTORY where fieldJ = "status";

初次尝试与问题 #

一开始尝试用LEFT JOIN关联表查询，SQL语句如下：

SELECT h.ID, h.P_KEY,h.fieldJ, h.created, h.fromString, h.toString, h2.created, h2.fromString, TIMEDIFF(h2.created,h.created) AS "TD"
FROM HISTORY AS h
LEFT JOIN HISTORY AS h2 ON h.toString = h2.fromString AND h2.created > h.created AND h.P_key = h2.P_key AND h2.fieldJ = "status" 
WHERE h.fieldJ = "status" 
ORDER BY h.created asc

但输出结果出现了重复匹配的问题，无法得到准确的间隔数据。

最终解决方案 #

通过子查询的方式解决了该问题，最终SQL语句如下：

SELECT 
AllData.ID, AllData.K, AllData.`Status`,  AllData.TIME1, AllData.TIME2, TIMEDIFF(AllData.TIME2,AllData.TIME1)
FROM
(
  SELECT 
    h1.P_Id AS 'ID', h1.P_key AS 'K', h1.created AS 'TIME1', 
    h1.toString AS 'Status',
    (
      SELECT h2.created FROM HISTORY AS h2 
      WHERE h2.created > h1.created 
      AND h2.fieldJ = "status" 
      AND h2.P_key = h1.P_Key LIMIT 1
    ) AS 'TIME2'
  FROM HISTORY AS h1
  WHERE fieldJ = "status"
  ) AS AllData 
ORDER BY ID

内容的提问来源于stack exchange，提问作者Christian