mysql - 根据下一行的条件选择一行

Question

这是我的 MySQL 表的一部分:

| id   | opentrap | openrect | lampgroen | lamprood | closeout | openout | limitswitchclose | limitswitchopen | traploop | photocells | rectloop | DateTime            | milliseconds |
|   92 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        1 |          1 |        1 | 2018-11-09 13:56:41 |          654 |
|   93 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        1 |          1 |        1 | 2018-11-09 13:56:42 |          262 |
|   94 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        0 |          0 |        1 | 2018-11-09 13:56:42 |          561 |
|   95 |        0 |        0 |         1 |        1 |        0 |       0 |                0 |               1 |        0 |          0 |        0 | 2018-11-09 13:56:45 |           83 |
|   96 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        1 |          0 |        0 | 2018-11-09 13:56:46 |          189 |
|   97 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        1 |          1 |        0 | 2018-11-09 13:56:47 |          402 |
|   98 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        0 |          1 |        1 | 2018-11-09 13:56:48 |          611 |
|   99 |        0 |        0 |         1 |        1 |        0 |       1 |                0 |               1 |        0 |          0 |        1 | 2018-11-09 13:56:48 |          916 |

我想根据很多条件选择一行，如果下一行有traploop = 1 或 rectloop = 1

我想使用的查询看起来像这样(但我不知道如何为下一行编写条件):

SELECT * 
FROM table 
WHERE closeout = 0 
    AND openout = 0 
    AND lampgroen = 1 
    AND lamprood = 1 
    AND limitswitchopen = 1 
    AND NEXTROW: traploop = 1 
    OR rectloop = 1;

这应该给我:

|   95 |        0 |        0 |         1 |        1 |        0 |       0 |                0 |               1 |        0 |          0 |        0 | 2018-11-09 13:56:45 |           83 |

Answer 1

这是一种使用相关子查询的方法:

SELECT *
FROM yourTable t1
WHERE closeout = 0 AND openout = 0 AND lampgroen = 1 AND lamprood = 1 AND
    limitswitchopen = 1 AND
    ((SELECT t2.traploop FROM yourTable t2
     WHERE t2.id > t1.id ORDER BY t2.id LIMIT 1) = 1 OR
    (SELECT t2.rectloop FROM yourTable t2
     WHERE t2.id > t1.id ORDER BY t2.id LIMIT 1) = 1);

Demo

这种方法对于 id 值可能不连续的可能性是稳健的。 “下一个”行的唯一要求是它具有当前行的下一个最高 id 值。