oracle 在SQL中减去前一行减去当前行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30500251/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 02:53:07 来源:igfitidea点击:
Subtracting previous row minus current row in SQL
提问by Gayathri
I have a table orders.
我有一张桌子orders。
How do I subtract previous row minus current row for the column Incoming?
如何减去该列的前一行减去当前行Incoming？
回答by wiretext
in My SQL
在我的 SQL
select a.Incoming,
coalesce(a.Incoming -
(select b.Incoming from orders b where b.id = a.id + 1), a.Incoming) as differance
from orders a
回答by Gunaseelan
回答by Frank Ockenfuss
create table orders (date_in date, incoming_vol number);
insert into orders values (to_date('27.05.2015', 'DD.MM.YYYY'), 83);
insert into orders values (to_date('26.05.2015', 'DD.MM.YYYY'), 107);
insert into orders values (to_date('25.05.2015', 'DD.MM.YYYY'), 20);
insert into orders values (to_date('24.05.2015', 'DD.MM.YYYY'), 7);
insert into orders values (to_date('22.05.2015', 'DD.MM.YYYY'), 71);
The LAG function is used to access data from a previous row
LAG 函数用于访问前一行的数据
SELECT DATE_IN,
incoming_vol as incoming,
LAG(incoming_vol, 1, incoming_vol) OVER (ORDER BY date_in) - incoming_vol AS incoming_diff
FROM orders
order by 1 desc
Another solution without analytical functions:
另一个没有分析功能的解决方案：
select o.date_in, o.incoming_vol, p.incoming_vol-o.incoming_vol
from orders p, orders o
where p.date_in = (select nvl(max(oo.date_in), o.date_in)
from orders oo where oo.date_in < o.date_in)
;
