Разница MySQL между двумя строками оператора SELECT

Я пытаюсь сделать разницу в двух строках в базе данных mysql.
У меня есть таблица с идентификаторами, километрами, датой, car_id, car_driver и т. Д. …
Поскольку я не всегда ввожу информацию в таблицу в правильном порядке, я могу получить такую ​​информацию:

ID | Kilometers | date | car_id | car_driver | ... 1 | 100 | 2012-05-04 | 1 | 1 2 | 200 | 2012-05-08 | 1 | 1 3 | 1000 | 2012-05-25 | 1 | 1 4 | 600 | 2012-05-16 | 1 | 1 

С помощью оператора select я могу правильно сортировать таблицу:

 SELECT * FROM mytable ORDER BY car_driver ASC, car_id ASC, date ASC 

Я получу это:

 ID | Kilometers | date | car_id | car_driver | ... 1 | 100 | 2012-05-04 | 1 | 1 2 | 200 | 2012-05-08 | 1 | 1 4 | 600 | 2012-05-16 | 1 | 1 3 | 1000 | 2012-05-25 | 1 | 1 

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

 ID | Kilometers | date | car_id | car_driver | number_km_since_last_date 1 | 100 | 2012-05-04 | 1 | 1 | 0 2 | 200 | 2012-05-08 | 1 | 1 | 100 4 | 600 | 2012-05-16 | 1 | 1 | 400 3 | 1000 | 2012-05-25 | 1 | 1 | 400 

Я думал о том, чтобы делать INNER JOIN, чтобы выполнять то, что я хотел, но у меня такое чувство, что я не могу присоединиться к моему ID, так как они не отсортированы правильно.
Есть ли способ достичь того, чего я хочу?

Должен ли я создать представление со своего рода row_number, которое затем можно использовать в моем INNER JOIN?

 SELECT mt1.ID, mt1.Kilometers, mt1.date, mt1.Kilometers - IFNULL(mt2.Kilometers, 0) AS number_km_since_last_date FROM myTable mt1 LEFT JOIN myTable mt2 ON mt2.Date = ( SELECT MAX(Date) FROM myTable mt3 WHERE mt3.Date < mt1.Date ) ORDER BY mt1.date 

Sql Fiddle

Или, путем эмуляции функции lag() через хакерство MySql ...

 SET @kilo=0; SELECT mt1.ID, mt1.Kilometers - @kilo AS number_km_since_last_date, @kilo := mt1.Kilometers Kilometers, mt1.date FROM myTable mt1 ORDER BY mt1.date 

Sql Fiddle

В Postgres, Oracle и SQL-Server 2012 это просто, используя функцию LAG() :

 SELECT id, kilometers, date, kilometers - COALESCE( LAG(kilometers) OVER (ORDER BY date ASC, car_driver ASC, id ASC) , kilometers) AS number_km_since_last_date FROM mytable ; 

В MySQL нам нужно сделать некоторые неприятные конструкции. Либо встроенный подзапрос (возможно, не очень хорошая производительность):

 SELECT id, kilometers, date, kilometers - COALESCE( ( SELECT p.kilometers FROM mytable AS p WHERE ( p.date = m.date AND p.car_driver = m.car_driver AND p.id < m.id OR p.date = m.date AND p.car_driver < m.car_driver OR p.date < m.date ) ORDER BY p.date DESC, p.car_driver DESC LIMIT 1 ), kilometers) AS number_km_since_last_date FROM mytable AS m ; 

или самосоединение (уже предоставленное @Michael Fredrickson) или использование переменных MySQL (уже предоставлено также).


Если вы хотите, чтобы счетчик снова начинал с 0 для каждого car_id , что было бы сделано с PARTITION BY во многих других СУБД:

 SELECT id, kilometers, date, kilometers - COALESCE( LAG(kilometers) OVER (PARTITION BY car_id ORDER BY date ASC, car_driver ASC, id ASC) , kilometers) AS number_km_since_last_date FROM mytable ; 

это можно сделать в MySQL следующим образом:

 SELECT id, kilometers, date, kilometers - COALESCE( ( SELECT p.kilometers FROM mytable AS p WHERE p.car_id = m.car_id AND ( p.date = m.date AND p.car_driver = m.car_driver AND p.id < m.id OR p.date = m.date AND p.car_driver < m.car_driver OR p.date < m.date ) ORDER BY p.date DESC, p.car_driver DESC LIMIT 1 ), kilometers) AS number_km_since_last_date FROM mytable AS m ; 

С несортированными данными я могу только думать о встроенном подзапросе (не очень хорошая идея на большой таблице):

 select t1.*, t1.Kilometers - (select top 1 kilometers from mytable t2 where t2.date < t1.date order by t2.date desc) as number_km_since_last_date from mytable t1 

Если вы отсортировали данные, вы можете использовать левое соединение

 select t1.* t1.Kilometers - t2.Kilometers as number_km_since_last_date from mytable t1 left join mytable t2 on t1.id = t2.id + 1 

Вероятно, вы можете сказать, что я больше человек TSQL, поэтому вам может потребоваться настроить синтаксис для MySQL.

Вот пример использования CURSOR для этого случая использования

 CREATE TABLE TEMP1 ( MyDate DATETIME, MyQty INT ) INSERT INTO TEMP1 VALUES ('01/08/17', 100) INSERT INTO TEMP1 VALUES ('01/09/17', 120) INSERT INTO TEMP1 VALUES ('01/10/17', 180) DECLARE @LastDate DATETIME = NULL DECLARE @LastQty INT = NULL DECLARE @MyDate DATETIME = NULL DECLARE @MyQty INT = NULL DECLARE mycursor CURSOR FOR SELECT MyDate, MyQty FROM TEMP1 ORDER BY MyDate OPEN mycursor FETCH NEXT FROM mycursor INTO @MyDate, @MyQty WHILE @@FETCH_STATUS = 0 BEGIN SELECT @MyDate, @MyQty - @LastQty SET @LastDate = @MyDate SET @LastQty = @MyQty FETCH NEXT FROM mycursor INTO @MyDate, @MyQty END CLOSE mycursor DEALLOCATE mycursor 
  • Вставка данных в таблицу с результатом другого запроса выбора
  • выбрать счетчик приращений в mysql
  • MySQL выбирает оператор с CASE или IF ELSEIF? Не уверен, как получить результат
  • Как получить значения из IGrouping
  • Сортировка по порядку значений в предложении select в разделе «in» в mysql
  • jQuery: Лучшая практика для заполнения выпадающего списка?
  • INSERT INTO ... SELECT для всех столбцов MySQL
  • Автоматическое изменение размера элемента SELECT в соответствии с выбранной шириной OPTION
  • Rails 3 - выберите с помощью Include?
  • Событие Click на элементе выбора опции в хроме
  • MySQL - SQL_BIG_SELECTS
  • Давайте будем гением компьютера.