sql - SQLite连接不起作用

Question

我有一个选择一些行的SQL，我希望它也选择我选择的每一行之后的行，我不知道如何编写这样的查询...

简单选择：

SELECT myIndex, scr, date from history where x.state = "Move to Test"

我最近的尝试：

SELECT myIndex, scr, date
from history as x cross join history as y on y.myIndex + 1 = x.myIndex
where state = "Move to Test"

给出：“模糊列名称：myIndex”

如果我要像这样指定列表；

SELECT x.myIndex, x.scr, x.date
from history as x cross join history as y on y.myIndex + 1 = x.myIndex
where x.state = "Move to Test"

我从“ y”表中一无所获...

这是我的输出应如下所示的示例：

391 200024   2006-11-27 16:03:43     Move to Test     < I am selecting this row
392 200024   2006-11-27 16:21:02     Dev              < I want to also select the *next* row, regardless of what's in it
395 200024   2006-11-29 10:58:17     Move to Test     < selected
396 200024   2006-11-29 12:10:29     Peer Review      < next
402 200024   2006-11-30 07:27:51     Move to Test     < selected
403 200024   2006-11-30 08:26:13     TQA              < next
408 200024   2007-05-03 08:35:40     Move to Test     < selected
409 200024   2007-05-03 08:52:53     Dev              < next

Answer 1

在SQLite 3.8.3或更高版本中：

WITH indexes AS (SELECT myIndex
                 FROM history
                 WHERE state = 'Move to Test')
SELECT myIndex,
       scr,
       date
FROM history
WHERE myIndex     IN indexes
   OR myIndex - 1 IN indexes

在早期版本中，不能使用CTE：

SELECT myIndex,
       scr,
       date
FROM history
WHERE myIndex     IN (SELECT myIndex FROM history WHERE state = 'Move to Test')
   OR myIndex - 1 IN (SELECT myIndex FROM history WHERE state = 'Move to Test')