mysql - 根据相邻行中字段的值在 mysql 中选择一行

Question

我需要能够根据前一行的值选择一行。给定表格:

id      value
---      ---
1        D
2        D
3        N
4        P
5        P
6        P

我只想选择 ID 为 4 的行，因为 ID = 3 的行的值为“N”

所以我需要这样做:

SELECT
 id as current_id,
 value
FROM
 table
WHERE
 (SELECT id FROM table WHERE "current_id" - 1) = "N"

Answer 1

试试这个:

SELECT id AS current_id
, value
FROM table
WHERE id IN (SELECT id + 1 FROM table WHERE value = "N") 

编辑:
使用非连续 ID 也很简单:

SELECT current_id, value
FROM (SELECT value
  , @old_id AS old_id
  , @old_id := id AS current_id
  FROM table, (SELECT @old_id := 0) AS v
  ORDER BY current_id) AS sq
WHERE exists (SELECT id FROM table AS o WHERE o.value = "N" AND o.id = sq.old_id) ;