Calculating Differences between Rows in a MySQL SELECT Query
To calculate the difference between rows in a SELECT query, consider the following approach:
Using a LEFT JOIN with a MAX() Subquery
You can use a LEFT JOIN to connect a row with its previous one, based on a condition. In your case, you want to find the previous row for each date:
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.dateUsing a Lag() Function Emulation
Since MySQL doesn't have a lag() function, you can emulate one using a user-defined variable:
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.dateResults
Both methods will produce the desired output:
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
The above is the detailed content of How can I calculate the difference between rows in a MySQL SELECT query?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
