sql查詢-有解無憂

1000 100
50         50
20           30
10           20
5             15

請教下大神，第二列前一行的值減去第一列的下一行值查為下一行第二列的值，如：第二列第一行值為100-第一列第二行值50，結果為第二列第二行值50
第二列第二行值為50-第一列第三行值20，結果為第二列第三行值30
第二列第三行值為30-第一列第四行值10，結果為第二列第四行值20

如此下推

請問該怎么查詢，萬分感謝！

uj5u.com熱心網友回復：

sum(col) over(order by id)

uj5u.com熱心網友回復：

一條sql陳述句貌似很難搞定，需要用到游標。

uj5u.com熱心網友回復：

with tmp as
( select 1 序號,1000 a,100 b from dual
union all select 2 序號,50 a,50 b from dual
union all select 3 序號,20 a,30 b from dual
union all select 4 序號,10 a,20 b from dual
union all select 5 序號,5 a,15 b from dual
)
select 序號,a,b,(lead(b) over(order by 序號 desc)-a) c from tmp
order by 序號;

大概這樣子吧,列名為"c"的那一列(值與第"b"值相等)
就是第二列的演算法

uj5u.com熱心網友回復：

如果你現在已經有了兩列的資料了，那么你這么查詢的目的是什么呢？

uj5u.com熱心網友回復：

第一列是指每次的消費金額，第二列結果是指余額

uj5u.com熱心網友回復：

是我沒描述清楚，第一列的值是已知的，第二列除了第一行的值是知道外，其他是求出來的

uj5u.com熱心網友回復：

with tmp as
(         select 1 id,1000 a,100 b from dual
union all select 2 id,50 a,null b from dual
union all select 3 id,20 a,null b from dual
union all select 4 id,10 a,null b from dual
union all select 5 id,5  a,null b from dual
)
select id,a,b from tmp where id=1
union all
select id,a,(select b from tmp where id=1)-sum(b) over(order by id rows between unbounded preceding and current row)
  from tmp where id>1;

uj5u.com熱心網友回復：

參考 7 樓 mayanzs 的回復:

with tmp as
(         select 1 id,1000 a,100 b from dual
union all select 2 id,50 a,null b from dual
union all select 3 id,20 a,null b from dual
union all select 4 id,10 a,null b from dual
union all select 5 id,5  a,null b from dual
)
select id,a,b from tmp where id=1
union all
select id,a,(select b from tmp where id=1)-sum(b) over(order by id rows between unbounded preceding and current row)
  from tmp where id>1;

把標紅的 sum(b) 改為 sum(a) 就可以了，其他的不變

uj5u.com熱心網友回復：

抱歉，筆誤。

uj5u.com熱心網友回復：

with t as(
select 1000 as c1,100 as c2 from dual
union all select 50 as c1,50 as c2 from dual
union all select 20 as c1,30 as c2 from dual
union all select 10 as c1,20 as c2 from dual
union all select 5 as c1,15 as c2 from dual
)
select t.* from(
select rownum as rw,t.* from t) t left join (select rownum-1 as rw,t.* from t) t1 on t1.rw=t.rw
where t.c2-t1.c1=t1.c2

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/117704.html

標籤：開發

上一篇：用SQL設定頁數的問題。求解

下一篇：存盤程序