MySQL SQL realizes the addition and subtraction of up and down lines

Test table:

CREATE TABLE `test` (`id` int (11) NOT NULL auto_increment, `value` int (11) default NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Feel free to add test data:

Mysql > SELECT * FROM test;+-+-+ | id | value | +-+-+ | 1 | 2 | 2 | 5 | 3 | 6 | 4 | 10 | +-+-+

Objective:

Row2-row1

Row3-row2

Row4-row3 and so on, output.

First make a self-connection, the connection condition is id1=id2+1, so that id1 happens to be the next self-increment of id2. Mysql > SELECT a.id id1, b.id id2, a.value value1, b.value value22- > FROM test a JOIN test b ON a.id = b.id + 1 +-+ | id1 | id2 | value1 | value2 | +-+ | 2 | 1 | 5 | 2 | 3 | 2 | 6 | 5 | 4 | 3 | 10 | 6 | +- -+-+ 3 rows in set (0.00 sec)

Because id1 is the next self-increment of id2, value1-value2 subtracts to the final result.

Write the above self-join result set into a temporary table, and subtract value1 from value2 to get the final result:

Mysql > SELECT value1-value2 RESULT- > FROM (- > SELECT a.value value1, b.value value2- > FROM test a JOIN test b ON a.id = b.id + 1->) t makesi + | RESULT | +-+ | 3 | | 1 | 4 | +-+ 3 rows in set (0.00 sec)

3, 1 and 4 are 5-2, 6-4 and 10-6.

In the same way, you have to add up and down.

Author's official account on Wechat (continuously updated)