Выбор нескольких столбцов из предыдущей строки в MySQL

Question

Предположим, у меня есть такая таблица:

| id |       date | name | value |
|----|------------|------|-------|
|  0 | 2017-01-14 |  foo |   one |
|  1 | 2017-01-17 |  bar |   two |
|  2 | 2017-01-18 | john |  five |
|  3 | 2017-01-19 |  doe |   ten |

(где date не обязательно заказывать обязательно)

Я хочу иметь возможность выбрать некоторые значения из предыдущего ряда (на основе date). Такая функциональность может быть достигнута с помощью следующего запроса:

SELECT 
  *, 
  (SELECT 
     name 
   FROM 
     example e2 
   WHERE 
     e2.dt < e1.dt 
   ORDER BY dt DESC 
   LIMIT 1
   ) as prev_name 
FROM example e1

с полученной таблицей:

| id |         dt | name | value | prev_name |
|----|------------|------|-------|-----------|
|  0 | 2017-01-14 |  foo |   one |    (null) |
|  1 | 2017-01-17 |  bar |   two |       foo |
|  2 | 2017-01-18 | john |  five |       bar |
|  3 | 2017-01-19 |  doe |   ten |      john |

Теперь это работает просто отлично. Тем не менее, было бы предпочтительнее, если бы я мог легко выбрать несколько столбцов из предыдущей строки, что привело бы к такому результату:

| id |         dt | name | value | prev_name | prev_value |    prev_dt |
|----|------------|------|-------|-----------|------------|------------|
|  0 | 2017-01-14 |  foo |   one |    (null) |     (null) |     (null) |
|  1 | 2017-01-17 |  bar |   two |       foo |        one | 2017-01-14 |
|  2 | 2017-01-18 | john |  five |       bar |        two | 2017-01-17 |
|  3 | 2017-01-19 |  doe |   ten |      john |       five | 2017-01-18 |

Конечно, это можно сделать, просто скопировав подзапрос (SELECT [..] FROM example e2 ...) в запрос несколько раз, но я думаю, что это не самый предпочтительный путь. Я нашел несколько вопросов о том, как обращаться к проблеме "как выбрать записи из предыдущей строки" или "как выбрать несколько столбцов с помощью подзапросов", но не к обеим. Последняя проблема в основном решается с помощью JOIN утверждение, но я думаю, что это не сочетается с делом "предыдущий ряд". Итак, мой вопрос: что может быть лучше для получения последнего результата, чем копировать подзапрос для каждого столбца, который нам нужен?

РЕДАКТИРОВАТЬ. В качестве дополнительного ограничения, которое я не включил в исходный вопрос, "предыдущий" может фактически отличаться от предыдущего ряда, а скорее "предыдущий ряд, удовлетворяющий условию". Итак, предположим, что моя таблица содержит дополнительную boolean колонка b

| id |         dt | name | value | b |
|----|------------|------|-------|---|
|  0 | 2017-01-14 |  foo |   one | 1 |
|  1 | 2017-01-17 |  bar |   two | 0 |
|  2 | 2017-01-18 | john |  five | 1 |
|  3 | 2017-01-19 |  doe |   ten | 0 |

Я хотел бы, чтобы "предыдущая строка" была предыдущей строкой с b = 1 так что желаемый результат будет:

| id |         dt | name | value | b | prev_name | prev_value |    prev_dt |
|----|------------|------|-------|---|-----------|------------|------------|
|  0 | 2017-01-14 |  foo |   one | 1 |    (null) |     (null) |     (null) |
|  1 | 2017-01-17 |  bar |   two | 0 |       foo |        one | 2017-01-14 |
|  2 | 2017-01-18 | john |  five | 1 |       foo |        one | 2017-01-14 |
|  3 | 2017-01-19 |  doe |   ten | 0 |      john |       five | 2017-01-18 |

Я думаю, что это все еще может быть достигнуто ответом Джеймса Скотта, просто обновляя переменные, когда b = 1, используя IF, но, возможно, в этом случае возможно другое решение.

РЕДАКТИРОВАТЬ. SQLfiddle

Answer 1

Примерно так будет возвращаться идентификатор "предыдущей" строки.

 SELECT x.*
      , MAX(y.id) prev_id
   FROM example x
   LEFT
   JOIN example y
     ON y.id < x.id 
    AND y.b = 1
  GROUP
     BY x.id;

Я оставлю бизнес по возвращению остальных данных, связанных с этой строкой, в качестве упражнения для читателя.

Answer 2

Похоже, хороший вариант использования для переменных сеанса, если вы хотите только предыдущую строку, вы можете использовать ORDER BY чтобы получить разные результаты.

SET @VDt := NULL, @VName := NULL, @VValue := NULL;

SELECT  id, @VName prev_name, @VValue prev_value, @VDt prev_dt, @VDt := dt dt, @VName := `name` `name`, @VValue := `value` `value` FROM example;

Запутался, когда я впервые опубликовал, обратите внимание, что переменные должны быть установлены после того, как они возвращаются из предыдущей строки. Чтобы изменить порядок столбцов (при желании), вы можете заключить этот запрос в другой, который затем упорядочит столбцы результатов.

Дай мне знать если тебе нужно что-нибудь еще,

С Уважением,

Джеймс