MySQL用select输出两行,SELECT语句的两行之间的MySQL差异

I am trying to make the difference of two rows in an mysql database.

I have this table containing ID, kilometers, date, car_id, car_driver etc...

Since I don't always enter the information in the table in the correct order, I may end up with information like this:

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

With a select statement I am able to sort my table correctly:

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

I will obtain this:

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

Now I would like to make a view where basically I have this extra information: Number of kilometers since last date and I would like to obtain something like this:

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

I thought of doing an INNER JOIN to perform what I wanted, but I have the feeling I can't do the join on my ID since they are not sorted correctly.

Is there a way to achieve what I want?

Shall I create a view with a sort of row_number that I can then used in my 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

Or, by emulating a lag() function through MySql hackiness...

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