在SQL中从另一行减去一行数据 [英] Subtracting one row of data from another in SQL

问题描述

我已经迷上了一些SQL，其中有几行数据，我想从上一行中减去一行，并使其一直向下重复.

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.

所以这是表格:

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         |

我尝试了自我连接，但是我不确定如何限制结果，而不是让结果遍历自己.我不能确定id值对于给定的结果集将是连续的，因此我不使用该值.我可以将架构扩展为包括某种顺序值.

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.

这是我尝试过的:

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

谢谢您的帮助.

推荐答案

这可能会对您有所帮助.

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

这篇关于在SQL中从另一行减去一行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！