MySQL SELECT语句两行之间的MySQL差异

Question

提问by user1108276

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:

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

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 语句，我可以正确地对表进行排序：

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?

我想过做一个 INNER JOIN 来执行我想要的，但我觉得我无法在我的 ID 上进行连接，因为它们没有正确排序。
有没有办法实现我想要的？

Shall I create a view with a sort of row_number that I can then used in my INNER JOIN?

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

Answer 1

回答by Michael Fredrickson

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

Sql小提琴

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

或者，lag()通过 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

Sql Fiddle

Sql小提琴

Answer 2

回答by ypercube??

In Postgres, Oracle and SQL-Server 2012, this is plain simple, using the LAG()function:

在 Postgres、Oracle 和 SQL-Server 2012 中，这很简单，使用LAG()函数：

SELECT
    id, kilometers, date,
    kilometers 
    - COALESCE( LAG(kilometers) OVER (ORDER BY date ASC, car_driver ASC, id ASC)
              , kilometers) 
        AS number_km_since_last_date
FROM
    mytable ;

In MySQL, we have to do some nasty constructions. Either an inline subquery (with probably not very good performance):

在 MySQL 中，我们必须做一些讨厌的构造。内联子查询（性能可能不是很好）：

SELECT
    id, kilometers, date,
    kilometers - COALESCE(
            ( SELECT p.kilometers
              FROM mytable AS p
              WHERE ( p.date = m.date AND p.car_driver = m.car_driver
                                                     AND p.id < m.id
                   OR p.date = m.date AND p.car_driver < m.car_driver
                   OR p.date < m.date
                    )
              ORDER BY p.date DESC, p.car_driver DESC
                  LIMIT 1
            ), kilometers) 
        AS number_km_since_last_date
FROM
    mytable AS m ;

or a self-join (already provided by @Michael Fredrickson) or using MySQL variables (already provided as well).

或自联接（已由@Michael Fredrickson 提供）或使用 MySQL 变量（也已提供）。

If you want the counter to start again from 0 for every car_id, which would be done with PARTITION BYin many other DBMS:

如果您希望计数器从 0 重新开始car_id，这将PARTITION BY在许多其他 DBMS 中完成：

SELECT
    id, kilometers, date,
    kilometers 
    - COALESCE( LAG(kilometers) OVER (PARTITION BY car_id 
                                      ORDER BY date ASC, car_driver ASC, id ASC)
              , kilometers) 
        AS number_km_since_last_date
FROM
    mytable ;

it could be done in MySQL like this:

它可以像这样在 MySQL 中完成：

SELECT
    id, kilometers, date,
    kilometers - COALESCE(
            ( SELECT p.kilometers
              FROM mytable AS p
              WHERE p.car_id = m.car_id
                AND ( p.date = m.date AND p.car_driver = m.car_driver
                                                     AND p.id < m.id
                   OR p.date = m.date AND p.car_driver < m.car_driver 
                   OR p.date < m.date
                    )
              ORDER BY p.date DESC, p.car_driver DESC
                  LIMIT 1
            ), kilometers) 
        AS number_km_since_last_date
FROM
    mytable AS m ;

Answer 3

回答by Alex Furman

With data unsorted I can only think of inline subquery (not a good idea on the large table):

对于未排序的数据，我只能想到内联子查询（在大表上不是一个好主意）：

select t1.*,
t1.Kilometers - (select top 1 kilometers from mytable t2 where t2.date < t1.date order by t2.date desc) as number_km_since_last_date
from mytable t1

If you get data sorted you can use left join

如果您对数据进行排序，则可以使用左连接

select t1.*
t1.Kilometers - t2.Kilometers as number_km_since_last_date
from mytable t1
left join mytable t2
  on t1.id = t2.id + 1

You can probably tell that I'm more of a TSQL guy so you might need to adjust syntax for MySQL.

您可能会说我更喜欢 TSQL，因此您可能需要调整 MySQL 的语法。

Answer 4

回答by Tarunpreet Ubhi

Here's an example of using CURSOR for this use case as well

以下是在此用例中使用 CURSOR 的示例

CREATE TABLE TEMP1
(
    MyDate DATETIME,
    MyQty INT
)

INSERT INTO TEMP1 VALUES ('01/08/17', 100)
INSERT INTO TEMP1 VALUES ('01/09/17', 120)
INSERT INTO TEMP1 VALUES ('01/10/17', 180)

DECLARE @LastDate DATETIME = NULL
DECLARE @LastQty INT = NULL
DECLARE @MyDate DATETIME = NULL
DECLARE @MyQty INT = NULL

DECLARE mycursor CURSOR FOR
SELECT MyDate, MyQty FROM TEMP1 ORDER BY MyDate
OPEN mycursor
FETCH NEXT FROM mycursor INTO @MyDate, @MyQty

WHILE @@FETCH_STATUS = 0  
BEGIN  

    SELECT @MyDate, @MyQty - @LastQty

    SET @LastDate = @MyDate
    SET @LastQty = @MyQty

FETCH NEXT FROM mycursor INTO @MyDate, @MyQty
END

CLOSE mycursor
DEALLOCATE mycursor

Answer 5

回答by Shaahiin

With MySQL 8 you can use CTE and ROW_NUMBER window function to make a more readable query

使用 MySQL 8，您可以使用 CTE 和 ROW_NUMBER 窗口函数来进行更具可读性的查询

WITH cte_name AS (
    SELECT
    ROW_NUMBER() OVER (ORDER BY update_time) as row_num,
    id,
    other_data,
    update_time
    FROM table_name WHERE condition = 'some_condition'
)
SELECT t2.id, t2.other_data, TIMEDIFF(t2.update_time, t1.update_time) AS time_taken
FROM
cte_name t1
JOIN cte_name t2 ON t1.row_num = t2.row_num-1
ORDER BY time_taken;

In this example I'm trying get the difference between datetime values.

在这个例子中，我试图获取日期时间值之间的差异。

The idea is to use ROW_NUMBER window function to assign an incremental number to each row after ordering by update_time.
The CTE allows us to write a subquery without having to repeat writing the same code.
We self join the CTE. The joining condition is basically - each n?? item of the second subquery joins with the n-1?? item of the first subquery (this also means the first row will disappear from the result set. if you need it you can use a UNION to add the first row to the start).

这个想法是使用 ROW_NUMBER 窗口函数在按 update_time 排序后为每一行分配一个增量编号。
CTE 允许我们编写子查询而不必重复编写相同的代码。
我们自己加入 CTE。加盟条件基本上是——每个n?? 第二个子查询的项与 n-1?? 第一个子查询的项目（这也意味着第一行将从结果集中消失。如果您需要它，您可以使用 UNION 将第一行添加到开头）。

There are some good tutorials for: CTE (Common Table Expression), ROW_NUMBERand even window functions

有一些很好的教程： CTE (Common Table Expression)，ROW_NUMBER甚至窗口函数

MySQL SELECT语句两行之间的MySQL差异

提问by user1108276

回答by Michael Fredrickson

Sql Fiddle

Sql小提琴

Sql Fiddle

Sql小提琴

回答by ypercube??

回答by Alex Furman

回答by Tarunpreet Ubhi

回答by Shaahiin

相关推荐

最近更新

标签