也许有人可以帮助开始解决这个问题。我有一个〉20 k行的表，其中包含以下列，它是一个日志表，用于管理日志lvl上的更改信息：
看看一种计算任务周期的方法，我想知道状态（field =“status”）的变化之间经过了多少时间。
一个任务可以有多个状态更改，所以我不能假装有一个开始和一个结束状态，然后只过滤这些状态。
每次状态更改时，我都想知道与上次状态更改相比过去了多少时间。
我正在使用MySQL。你知道我该怎么开始吗如果你需要更多的信息，让我知道。
谢谢！
编辑：
这里是当前的表结构：

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

RESULT:
P_ID;P_KEY;AUTHOR;Created;fieldJ;fromString;toString
P_ID;P_KEY;AUTHOR;Created;fieldJ;fromString;toString
10865;JIRA-176;Former user;2021-02-09 08:15:38;status;To Do;In Progress
10865;JIRA-176;Former user;2021-02-19 09:44:32;status;In Progress;In Test
10865;JIRA-176;Former user;2021-02-19 10:08:56;status;In Test;In Test
10865;JIRA-176;Former user;2021-03-26 09:54:15;status;In Test;Done
14223;JIRA-900;Former user;2023-02-02 19:41:46;status;To Do;IN REVIEW.
14223;JIRA-900;Former user;2023-02-07 20:19:12;status;IN REVIEW.;Refined
14223;JIRA-900;Former user;2023-02-09 09:58:02;status;Refined;To Do
14223;JIRA-900;Former user;2023-02-15 14:18:34;status;To Do;In Progress
14223;JIRA-900;Former user;2023-02-16 08:12:33;status;In Progress;Ready for QC
14223;JIRA-900;Former user;2023-02-16 13:29:03;status;Ready for QC;In Testing
14223;JIRA-900;Former user;2023-02-20 06:50:31;status;In Testing;Ready for UAT
14223;JIRA-900;Former user;2023-02-20 10:18:12;status;Ready for UAT;In UAT
14223;JIRA-900;Former user;2023-02-21 18:50:55;status;In UAT;Pre-Testing
14223;JIRA-900;Former user;2023-02-21 18:51:08;status;Pre-Testing;UAT In Test
14223;JIRA-900;Former user;2023-02-27 11:47:17;status;UAT In Test;Feasibility
14223;JIRA-900;Former user;2023-03-29 14:48:06;status;Feasibility;Ready for PROD
14223;JIRA-900;Former user;2023-03-29 16:59:45;status;Ready for PROD;IN PROD

**“正式用户”是一个或多个用户的占位符

预期结果：
表格

P_KEY    | status         | TIMEDIFF
JIRA-176 | In Progress    | 241:28:54 
JIRA-176 | In Test        | 00:24:24
JIRA-176 | In Test        | ...

到目前为止，我尝试的是：

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

WRONG OUTPUT:
ID;P_KEY;fieldJ;created;fromString;toString;created;fromString;TD
19133;JIRA-176;status;2021-02-09 08:15:38;To Do;In Progress;2021-02-19 09:44:32;In Progress;241:28:54
19221;JIRA-176;status;2021-02-19 09:44:32;In Progress;In Test;2021-03-26 09:54:15;In Test;838:59:59
19221;JIRA-176;status;2021-02-19 09:44:32;In Progress;In Test;2021-02-19 10:08:56;In Test;00:24:24
19224;JIRA-176;status;2021-02-19 10:08:56;In Test;In Test;2021-03-26 09:54:15;In Test;838:59:59

编辑2：
在我理解了如何管理数据之后，通过遵循这个Link, with the same problem，我找到了问题的解决方案。这里是我最后的查询：

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