mysql: 比较同一张表的相邻两行的某一字段的值

觉得这个问题比较有趣，发出来给有用的人参考：


/*Column Information For - test.tt*/
------------------------------------

Field Type Collation Null Key Default Extra Privileges Comment
------ ------- --------- ------ ------ ------- ------ ------------------------------- -------
id int(11) (NULL) YES (NULL) select,insert,update,references
value int(11) (NULL) YES (NULL) select,insert,update,references


SQL：
select a.id, a.value - b.value as diff from
(select id ,value, @num := @num + 1 as row_number from (SELECT @num:=0) r,tt order by id) a,
(select id ,value, @num2 := @num2 + 1 as row_number from (SELECT @num2:=0) r2, tt order by id) b
where a.row_number + 1 = b.row_number ;