表T:
--------------
编号:number
应发放:number
实发放:number
实发标志:varchar2(1)编号 应发放 实发放 实发标志
---------------------------------------
1 100.00 100.00 1
2 30.00 20.00 0
3 50.00 10.00 0
4 1000.00 501.00 0
5 10.00 10.00 1
...现在有500.00元可以发放,能否用一条SQL实现这种效果:编号 应发放 实发放 实发标志
---------------------------------------
1 100.00 100.00 1
2 30.00 30.00 1
3 50.00 50.00 1
4 1000.00 951.00 0
5 10.00 10.00 1
...谢谢!
--------------
编号:number
应发放:number
实发放:number
实发标志:varchar2(1)编号 应发放 实发放 实发标志
---------------------------------------
1 100.00 100.00 1
2 30.00 20.00 0
3 50.00 10.00 0
4 1000.00 501.00 0
5 10.00 10.00 1
...现在有500.00元可以发放,能否用一条SQL实现这种效果:编号 应发放 实发放 实发标志
---------------------------------------
1 100.00 100.00 1
2 30.00 30.00 1
3 50.00 50.00 1
4 1000.00 951.00 0
5 10.00 10.00 1
...谢谢!
where id in
(
select id
from
(
select id, sum(yf-sf)over(order by id) as num from tb
where sfflag=0
) where num<=500
)
2 30 30 1
3 50 50 1
4 1000 501 0
5 10 10 1
一条sql语句,可能很难搞定,楼主可以用游标+循环去修改
as
(
select 1 a, 100.00 b, 100.00 c, 1 d from dual
union all
select 2, 30.00, 20.00, 0 from dual
union all
select 3, 50.00, 10.00 , 0 from dual
union all
select 4 , 1000.00, 900.00 , 0 from dual
union all
select 5, 1000.00, 800.00 , 0 from dual
union all
select 6, 1000.00, 800.00 , 0 from dual
union all
select 7, 10.00, 10.00, 1 from dual
)
select a,b,c,case when d>0 then b else b+d end e
from
(
select a,b,c,d,lag(d)over(order by a) e
from
(
select a, b,c,(500-sum(b-c)over(order by a)) d,sum(b-c)over(order by a) as num from temp
where d=0
)
)--RESULT:2 30 20 30
3 50 10 50
4 1000 900 1000
5 1000 800 1000
6 1000 800 950--最后一列就是要更新的值!
---------- ---------- ---------- ----------
1 100 100 1
2 30 20 0
3 50 10 0
5 10 10 1
4 1000 501 0SQL> update t a
2 set flag=decode(sign((select sum(ap-p) from t where sno<=a.sno)-500),
3 -1,1,
4 0),
5 p=decode( sign((select sum(ap-p) from t where sno<=a.sno)-500) ,
6 -1,ap,
7 1,ap-((select sum(ap-p) from t where sno<=a.sno)-500)
8 )
9 where FLAG=0;3 rows updated.SQL> select * from t; SNO AP P FLAG
---------- ---------- ---------- ----------
1 100 100 1
2 30 30 1
3 50 50 1
4 1000 951 0
5 10 10 1SQL>
as
(
select 1 a, 100.00 b, 100.00 c, 1 d from dual
union all
select 2, 30.00, 20.00, 0 from dual
union all
select 3, 50.00, 10.00 , 0 from dual
union all
select 4 , 1000.00, 900.00 , 0 from dual
union all
select 5, 1000.00, 800.00 , 0 from dual
union all
select 6, 1000.00, 800.00 , 0 from dual
union all
select 7, 10.00, 10.00, 1 from dual
)
select a,b,c,case when d>0 then b else b+d end e
from
(
select a,b,c,d,lag(d)over(order by a) e
from
(
select a, b,c,(500-sum(b-c)over(order by a)) d,sum(b-c)over(order by a) as num from temp
where d=0
)
)--RESULT:2 30 20 30
3 50 10 50
4 1000 900 1000
5 1000 800 1000
6 1000 800 950
---------- -------- -------- --------
1 100.00 100.00 1
2 30.00 20.00 0
3 50.00 10.00 0
4 1000.00 501.00 0
5 10.00 10.00 1已用时间: 00: 00: 00.01
10:38:14 tina@PRACTICE> select 编号,to_char(应发放,9999.99) 应发放,
10:38:23 2 to_char(应发放 - case when sum(应发放-实发放)over(order by 应发放-实发放) > 500 then sum(应发放-实发放)over(order by 应发放-实发放) - 500 else 0 end,9999.99) 实发放,
10:38:23 3 case when case when sum(应发放-实发放)over(order by 应发放-实发放) > 500 then sum(应发放-实发放)over(order by 应发放-实发放) - 500 else 0 end > 0 then '0' else '1' end 实发标志
10:38:23 4 from t order by 1; 编号 应发放 实发放 实发标志
---------- -------- -------- --------
1 100.00 100.00 1
2 30.00 30.00 1
3 50.00 50.00 1
4 1000.00 951.00 0
5 10.00 10.00 1已用时间: 00: 00: 00.01
select id from(
select id,yf,sf,case when sflag>0 then yf else yf end kk
from
(
select id,yf,sf,sflag,lag(sflag)over(order by id) kk
from
(
select id, yf,sf,(500-sum(yf-sf)over(order by id)) sy,sum(yf-sf)over(order by id) as num,sflag from tb
where sflag=0
)
)
)
)