sql语句黑科技

mysql实现上下行相减

变量法

+---+------+------+
| id| num  | sub  |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    2 |    1 |
| 3 |    5 |    3 |
| 4 |    9 |    4 |
+---+------+------+

SELECT T.id, T.num, num - num2 AS DIFF
FROM (
SELECT A.id AS id, A.num AS num, @A.num AS num2, @A.num:= A.num
FROM TMP A, (SELECT @A.num:=0) B
) T