SQL查询以匹配发生在一组30秒时间段之间的事件。

Question

我正在尝试编写一个查询，在其中我希望获取30秒长的一组时间之间发生的事件。

示例：

如果我使用这个查询：

SELECT videotime FROM codes
    WHERE participant = 2 AND (retrospective ='y' AND forks > 0)

输出：

00:16:30.000
00:21:00.000
00:21:30.000
00:27:00.000
00:38:30.000

然后，我想在00:16:31，00:16:32，还有00:21:01，00:27:29.999等从另一张桌子上得到事件。

到目前为止，我所写的只是与第一行(00:16:30)相匹配，但我希望能够使它更匹配。

SELECT id, videotime FROM commands WHERE participant = 2 AND
videotime >= (SELECT videotime FROM codes
    WHERE participant = 2 AND (retrospective ='y' AND forks > 0) ) AND
videotime <= time((SELECT videotime FROM codes
    WHERE participant = 2 AND (retrospective ='y' AND forks > 0) ), '+30 seconds');

以上不正确的输出：

28          00:16:31.021
29          00:16:31.635
30          00:16:34.948
31          00:16:35.004
32          00:16:37.101
33          00:16:37.144
34          00:16:37.992
35          00:16:38.029
36          00:16:42.740
37          00:16:42.910
38          00:16:43.724
39          00:16:49.756
40          00:16:49.836
41          00:16:58.788
42          00:16:58.834
43          00:16:59.936

我需要做些什么来解决这个问题呢？

Answer 1

我对sqlite没有任何经验，所以我的回答很可能在语法上是不正确的，但希望基本点是清楚的：

SELECT id, videotime
FROM commands
WHERE participant = 2
      AND EXISTS
      ( SELECT time
        FROM (SELECT videotime 
              FROM codes
              WHERE participant = 2 
                    AND (retrospective ='y' AND forks > 0)) AS times
        WHERE commands.videotime - times.videotime >= 0
              AND commands.videotime - times.videotime <= 30
      )

(特别是以秒为单位的时间差的语法可能与我在这里所写的不同，但我希望您/某人能够相应地调整它)