起因
“我想要查[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 therecords 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, andperforms a calculation across the set of data in the window.[5][6]
