如何在两个连续的行之间没有连续ID的MySQL中的DATEDIFF？(How to DATEDIFF in MySQL between two consecutive rows not having

如何在两个连续的行之间没有连续ID的MySQL中的DATEDIFF？(How to DATEDIFF in MySQL between two consecutive rows not having consecutive IDs?)

目前我有一个查询给我来自表“日志”的这个结果：

S.ID S.INDEX E.ID TIME Secs 1 1 1 2018-05-14 16:07:48.527 2 2 1 2018-05-14 16:08:02.967 14 3 3 1 2018-05-14 16:08:21.750 19 10 1 2 2018-05-14 16:07:46.983 11 2 2 2018-05-14 16:08:00.883 14 12 3 2 2018-05-14 16:09:19.830 79 13 4 2 2018-05-14 16:09:49.907 30 29 1 3 2018-05-14 16:08:02.490 30 2 3 2018-05-14 16:08:06.717 04

“Secs”这一栏是在这里手写的，那些是我需要的值。 已经尝试使用DATEDIFF，但由于S.ID不连续（取决于E.ID），我没有得到正确的结果。

我如何使查询返回正确的值？ 我想我应该考虑E.ID，因为它是在“相同”递增期间唯一保持不变的，并且如果我在S.INDEX中看到1，我应该重新计数。

Currently I have a query giving me this results coming from table "Logs":

S.ID S.INDEX E.ID TIME Secs 1 1 1 2018-05-14 16:07:48.527 2 2 1 2018-05-14 16:08:02.967 14 3 3 1 2018-05-14 16:08:21.750 19 10 1 2 2018-05-14 16:07:46.983 11 2 2 2018-05-14 16:08:00.883 14 12 3 2 2018-05-14 16:09:19.830 79 13 4 2 2018-05-14 16:09:49.907 30 29 1 3 2018-05-14 16:08:02.490 30 2 3 2018-05-14 16:08:06.717 04

The column "Secs" was wrote by hand here, those are the values I need. Already tried to use DATEDIFF but since S.ID is not consecutive (it depends on E.ID) I'm not getting the right results.

How can I make a query to return the right values? I imagine I should take E.ID in consideration since it's the only thing that remains the same during the "same" incrementation and that if I see a 1 in S.INDEX I should reset counting.

最满意答案

只要在我原来的评论中描述的模式得到保证，这应该起作用：

SELECT a.*, TO_SECONDS(a.TIME) - TO_SECONDS(b.TIME) AS `Secs` FROM theTable AS s LEFT JOIN theTable AS b ON a.`e.id` = b.`e.id` AND a.`s.id` = b.`s.id` + 1 ;

此外，如果不同的e.id值的s.id值之间总是有差距，e.id比较实际上并不需要，但仍然使得查询的意图更清晰。

This should work, as long as the pattern I described in my original comment is guaranteed:

SELECT a.*, TO_SECONDS(a.TIME) - TO_SECONDS(b.TIME) AS `Secs` FROM theTable AS s LEFT JOIN theTable AS b ON a.`e.id` = b.`e.id` AND a.`s.id` = b.`s.id` + 1 ;

Additionally, if there is always a gap between different e.id value's s.id values, the e.id comparison is not actually needed, though still makes the intent of the query clearer.