在SQL中从另一行数据中减去一行数据

我被一些 SQL 难住了，其中我有几行数据，我想从前一行中减去一行并让它一直重复.

这是表:

创建表 foo (ID，长度)

INSERT INTO foo (id,length) VALUES(1,1090)INSERT INTO foo (id,length) VALUES(2,888)INSERT INTO foo (id,length) VALUES(3,545)INSERT INTO foo (id,length) VALUES(4,434)插入 foo (id,length) VALUES(5,45)

我希望结果显示名为差异的第三列，即从下面一行中减去一行，最后一行从零中减去.

<前>+------+------------------------+|id |长度 |区别|+------+------------------------+|1 |1090 |202 ||2 |第888话第343话|3 |第545话111 ||4 |第434话第389话|5 |45 |45 |

我已经尝试过自连接，但我不确定如何限制结果而不是让它自行循环.对于给定的结果集，我不能确定 id 值是连续的，所以我没有使用该值.我可以扩展架构以包含某种顺序值.

这是我试过的:

SELECT id, f.length, f2.length, (f.length - f2.length) AS 差异FROM foo f, foo f2

感谢您的帮助.

解决方案

这可能对您有所帮助.

<前><代码>选择a.id，a.length，合并(a.length -(select b.length from foo b where b.id = a.id + 1), a.length) 作为差异来自 foo a

I've been stumped with some SQL where I've got several rows of data, and I want to subtract a row from the previous row and have it repeat all the way down.

So here is the table:

CREATE TABLE foo (
  id,
  length
)

INSERT INTO foo (id,length) VALUES(1,1090)
INSERT INTO foo (id,length) VALUES(2,888)
INSERT INTO foo (id,length) VALUES(3,545)
INSERT INTO foo (id,length) VALUES(4,434)
INSERT INTO foo (id,length) VALUES(5,45)

I want the results to show a third column called difference which is one row subtracting from the one below with the final row subtracting from zero.

+------+------------------------+
| id   |length |  difference  |
+------+------------------------+
|    1 | 1090  |  202         |
|    2 |  888  |  343         |
|    3 |  545  |  111         |
|    4 |  434  |  389         |
|    5 |   45  |   45         |

I've tried a self join but I'm not exactly sure how to limit the results instead of having it cycle through itself. I can't depend that the id value will be sequential for a given result set so I'm not using that value. I could extend the schema to include some kind of sequential value.

This is what I've tried:

SELECT id, f.length, f2.length, (f.length - f2.length) AS difference
FROM foo f, foo f2

Thank you for the assist.

解决方案

This might help you (somewhat).

select a.id, a.length, 
coalesce(a.length - 
    (select b.length from foo b where b.id = a.id + 1), a.length) as diff
from foo a