起因

“我想要查[T表]，按[a字段]排序，并且每行结果都要和上一行[a字段]做差计算的结果”
-- 源于一个同学的需求

问题剖析

原表                                输出表                                      中间表

+-------+------+                    +-------+------+-------+                   +-------+------+-----+
| id    | a    |                    | id    | a    | minus |                   | id    | a    | seq | 
+-------+------+                    +-------+------+-------+                   +-------+------+-----+ 
| 10004 |    1 |                    | 10005 |    1 |     0 |                   | 10004 |    1 |   1 | 
| 10005 |    1 |           =>       | 10003 |    2 |     1 |           =>      | 10005 |    1 |   2 | 
| 10003 |    2 |                    | 10002 |    3 |     1 |                   | 10003 |    2 |   3 | 
| 10002 |    3 |                    | 10001 |    9 |     6 |                   | 10002 |    3 |   4 | 
| 10001 |    9 |                    +-------+------+-------+                   | 10001 |    9 |   5 | 
+-------+------+                                                               +-------+------+-----+ 

这个问题在MySQL( MySQL 8.0 )之前，可以间接构造一张中间表来处理（忘了从哪篇文章看到的这种解决问题的思路），在根据中间表查询得到输出表。下面给出从原表到中间表的样例SQL：

mysql> select t1.*, count(*) as seq from 
(Select id,a from T order by a,id) as t1 
join 
(Select id,a from T order by a,id) as t2
where t1.a > t2.a or (t1.a=t2.a and t1.id >= t2.id) group by t1.id /*,t1.a*/ order by t1.a,t1.id,seq;
+-------+------+-----+
| id    | a    | seq |
+-------+------+-----+
| 10004 |    1 |   1 |
| 10005 |    1 |   2 |
| 10003 |    2 |   3 |
| 10002 |    3 |   4 |
| 10001 |    9 |   5 |
+-------+------+-----+

其他

这个问题本质是行数据和上下数据（窗口？）的计算
实际上在SQLServer，MySQL（更高版本），PostgreSQL有支持到窗口函数
上面的中间表只用一个rank()函数 就可以得到。

维基上的解释
https://en.wikipedia.org/wiki/SQL_window_function

SQL window function
In the SQL database query language, window functions allow access to data in the records right before and after the current record.^[1][2][3][4] A window function defines a frame or window of rows with a given length around the current row, and performs a calculation across the set of data in the window.^[5][6]