目前我有一个查询给我来自表“日志”的这个结果:
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 04The 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.
更多推荐
发布评论