Home  >  Article  >  Database  >  How can I calculate the difference between rows in a MySQL SELECT query?

How can I calculate the difference between rows in a MySQL SELECT query?

DDD
DDDOriginal
2024-11-07 01:42:02281browse

How can I calculate the difference between rows in a MySQL SELECT query?

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.date

Using 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.date

Results

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn