查询以查找Mysql中连续行之间的差异

As I am new to MySQL, this question may be silly. How can I find the difference between successive rows?
Example:
A table (tableName='Abc') contains a single row as follows,

由于我是MySQL新手,这个问题可能很愚蠢。如何找到连续行之间的差异?示例:表(tableName ='Abc')包含单行,如下所示,

|DATA|
|10  |
|20  |
|30  |
|40  |
|50  |

Here I want get the output as,

在这里,我想得到输出,

|Dif|
|10 |
|10 |
|10 |
|10 |

How to find the difference like this without any index (primary or Auto_increment)?

如何在没有任何索引(primary或Auto_increment)的情况下找到这样的差异?

3 个解决方案

#1

A self-join is one way to compare consecutive rows:

自联接是比较连续行的一种方法:

SELECT
    MIN(T2.DATA - T1.DATA) AS Dif
FROM
    Abc T1 INNER JOIN Abc T2 on T1.DATA < T2.DATA
ORDER BY
    T1.DATA

#2

Use user-defined variables:

使用用户定义的变量:

SET @prevValue:=0;
SELECT value-@prevValue AS diff,@prevValue:=value FROM t;

You'll have to discard the first row for your case.

你必须丢弃你案件的第一行。

If the table contains both value and diff fields, you can set the diff field with:

如果表包含value和diff字段,则可以使用以下命令设置diff字段:

SET @prevValue:=0;
UPDATE t SET diff=IF((@d:=value-@prevValue) AND (@prevValue:=value),@d,@d);

Using the IF is the only way I could find to both set diff and update the @prevValue variable.

使用IF是我能找到设置diff并更新@prevValue变量的唯一方法。

#3

It would be best to do this outside of the DB by keeping track of the previous row. Here's some code (hopefully my php is not too rusty):

最好通过跟踪前一行来在DB之外执行此操作。这里有一些代码(希望我的php不会太生锈):

<?php

$prevNum = 0;

$db = mysql_connect(...); // fill in connection string

mysql_select_db("my_db", $db);

$result = mysql_query("select DATA from Abc order by DATA");

while ($row = mysql_fetch_array($result)) {

    $diff = $row[0] - $prevNum;

    echo "$diff\n";

    $prevNum = $row[0];

}

?>

If you need to do it within the db for some reason then it would probably be best to create a stored procedure which will basically do the same thing: instantiate a variable with value 0, report the difference for each row and that variable, then set the variable to the row value.

如果由于某种原因需要在db中执行它,那么创建一个基本上会执行相同操作的存储过程可能是最好的:实例化一个值为0的变量,报告每行和该变量的差异,然后设置变量到行值。

Edited to add an order by clause as noted by John Pick

编辑添加John Pick所述的order by子句

#1