MySQL 在SQL中从另一行数据中减去一行数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/919136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtracting one row of data from another in SQL
提问by Doc Falken
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.
我一直被一些 SQL 难住了，我有几行数据，我想从前一行中减去一行并让它一直重复。
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.
我已经尝试过自我加入，但我不确定如何限制结果而不是让它自行循环。我不能确定 id 值对于给定的结果集是连续的，所以我没有使用该值。我可以扩展模式以包含某种顺序值。
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.
谢谢你的帮助。
回答by shahkalpesh
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
回答by TheVillageIdiot
Yipee!!! this does the trick:
伊皮！！！这是诀窍：
SELECT f.id, f.length,
(f.length - ISNULL(f2.length,0)) AS diff
FROM foo f
LEFT OUTER JOIN foo f2
ON f2.id = (f.id +1)
Please check for other cases also, it is working for the values you posted! Note this is for SQL Server 2005
还请检查其他情况，它适用于您发布的值！请注意，这是针对 SQL Server 2005
回答by Cade Roux
So they are just ordered largest to smallest?
所以他们只是从大到小排序？
SELECT f.id, f.length, (f.length - ISNULL(t.length, 0)) AS difference
FROM foo AS f
LEFT JOIN (
SELECT f1.id
,MAX(f2.length) as length
FROM foo AS f1
INNER JOIN foo AS f2
ON f1.length > f2.length
GROUP BY f1.id
) AS t -- this is the triangle
ON t.id = f.id
You can use COALESCE(or IFNULL) instead of ISNULLfor MySQL.
您可以使用COALESCE(或IFNULL) 代替ISNULLMySQL。
回答by Jeff Meatball Yang
Select f1.id, f1.seqnum, f2.seqnum, f1.length, f2.length, f1.length-f2.length
From (
Select Id, length, row_number(order by length) 'seqnum'
From
foo
) f1
Inner join (
Select
Id, length, row_number(order by length) 'seqnum' from foo union select 0, 0, 0
) f2
On f1.seqnum = f2.seqnum + 1
Order by f1.length desc
回答by Josh
What about something like this:
像这样的事情怎么样：
SELECT T2.ID, T2.[Length], T2.[Length]-T1.[Length] AS 'Difference'
FROM Foo AS T1 RIGHT OUTER JOIN Foo AS T2 ON ( T1.ID = (T2.ID-1) )
ORDER BY T1.ID
回答by Jonathan Fingland
edit: fixed when re-read Q (misunderstood)
编辑：重读 Q 时修复（被误解）
SELECT f.id,
f2.id,
f.length,
f2.length,
(f.length -f2.length) AS difference
FROM foo f,
foo f2
where f2.id = f.id+1
id was ambiguous
id 不明确
edit: note: tested in mysql 5.0
编辑：注意：在 mysql 5.0 中测试
回答by carsten
I had this problem and it was interesting to look at your solutions. I find it strange that such a normal-life problem is so complicated in SQL. As I need the values in a report only, I chose a completely different solution. I'm running Ruby on Rails as the front end of my sqlite3 database, and just did the subtraction in the view like this:
我遇到了这个问题，看看你的解决方案很有趣。我觉得奇怪的是，SQL 中这样一个正常生活的问题是如此复杂。由于我只需要报告中的值，我选择了一个完全不同的解决方案。我正在运行 Ruby on Rails 作为我的 sqlite3 数据库的前端，并且只是在视图中进行了减法，如下所示：
In your ruby controller, there is a object variable @foo that holds the rows returned by your query.
在您的 ruby 控制器中，有一个对象变量 @foo 保存您的查询返回的行。
In the view, just do
在视图中，只需执行
<table border=1>
<tr>
<th>id</th>
<th>length</th>
<th>difference</th>
</tr>
<% for i in [email protected] do %>
<tr>
<td><%=h @foo[i].id %></td>
<td><%=h @foo[i].length %></td>
<td><% if ([email protected]) then %>
<%= @foo[i].length %>
<% else %>
<%= @foo[i+1].length.to_i - @foo[i].length.to_i %>
<% end %>
</td>
</tr>
<% end %>
</table>
Seems to be more robust than the SQL solutions.
似乎比 SQL 解决方案更健壮。
