SELECT语句两行之间的MySQL差异

2021-11-20 00:00:00 select mysql inner-join

我正在尝试区分 mysql 数据库中的两行.
我有一张包含 ID、公里数、日期、car_id、car_driver 等的表...
由于我并不总是以正确的顺序在表格中输入信息，我最终可能会得到这样的信息:

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

使用 select 语句，我可以正确地对表格进行排序:

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

我会得到这个:

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

我想通过 INNER JOIN 来执行我想要的操作，但我感觉我无法在我的 ID 上进行连接，因为它们没有正确排序.
有没有办法实现我想要的?

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?

我应该创建一个带有某种 row_number 的视图，然后我可以在我的 INNER JOIN 中使用它吗?

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

Sql Fiddle

或者，通过 MySql hackiness 模拟 lag() 函数...

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

相关文章