SELECT语句的两行之间的MySQL差异 [英] MySQL difference between two rows of a SELECT Statement

问题描述

我正在尝试使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小提琴

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

Sql Fiddle

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

这篇关于SELECT语句的两行之间的MySQL差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！