有表cdt_temp1 表字段名和对应数据记录如下:
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 60.58 2011082910087 20110210 0
20110401 420106197611114028 019570200021601 2010-05-27 996020.66 203.16 2011082910081 20110211 0字段解释:
同一IDNUM下可以有多个LENDID
DKFFR为日期字段
DKZJE为对应PRODUCTID对应的可分摊总金额
SJDK_BALANCE为每条记录最终得到的分摊金额,初始值都为0要求在对金额进行分摊时需要注意如下原则:
1、按时间顺序先后来分摊,即按DKFFR优先从日期小的开始分摊
2、每个LENDID对应的分摊金额不能大于BALANCE
3、每个PRODUCTID对应的分摊金额汇总应该等于该PRODUCTID对应的DKZJE
用sql语句分摊完金额,更新至SJDK_BALANCE字段,则结果应该为
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 13311.94 2011082910088 20110222 3639.42
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 60.58 2011082910087 20110210 60.58
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 13311.94 2011082910088 20110222 9672.52
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 60.58 2011082910087 20110210 0
20110401 420106197611114028 019570200021601 2010-05-27 996020.66 203.16 2011082910081 20110211 203.16CSDN上有大神能写出这个更新SQL语句吗?
我写了一个,但是更新的有问题,没有达到我要的效果。大神帮我看看,或者重写一个能达到我要求的语句。我的语句如下: --
declare
v_totall_m number; ----可分配金额
sydkje number; ----该笔剩余多少金额没抵扣
begin
for c1 in (select distinct idnum,productid from cdt_temp1) loop
select dkzje into v_totall_m from cdt_temp1 t where t.idnum = c1.idnum and t.productid = c1.productid and rownum <2;
for v_c in (select idnum,productid,lendid,balance from cdt_temp1 t where t.idnum = c1.idnum and t.productid = c1.productid order by dkffr asc) loop
---该笔剩余多少没抵扣
select t.balance-(select yjdkje from (select lendid,sum(t.sjdk_balance) yjdkje from cdt_temp1 t group by t.lendid) where lendid=v_c.lendid) into sydkje from cdt_temp1 t where lendid=v_c.lendid and rownum <2;
if v_totall_m - sydkje >= 0 then
update cdt_temp1 set sjdk_balance = v_c.balance where lendid = v_c.lendid and idnum = c1.idnum;
v_totall_m:= v_totall_m - v_c.balance;
else
update cdt_temp1 set sjdk_balance = v_totall_m where lendid = v_c.lendid and idnum = c1.idnum;
goto l1;
end if;
end loop;
<<l1>>
commit;
end loop;
end
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 60.58 2011082910087 20110210 0
20110401 420106197611114028 019570200021601 2010-05-27 996020.66 203.16 2011082910081 20110211 0字段解释:
同一IDNUM下可以有多个LENDID
DKFFR为日期字段
DKZJE为对应PRODUCTID对应的可分摊总金额
SJDK_BALANCE为每条记录最终得到的分摊金额,初始值都为0要求在对金额进行分摊时需要注意如下原则:
1、按时间顺序先后来分摊,即按DKFFR优先从日期小的开始分摊
2、每个LENDID对应的分摊金额不能大于BALANCE
3、每个PRODUCTID对应的分摊金额汇总应该等于该PRODUCTID对应的DKZJE
用sql语句分摊完金额,更新至SJDK_BALANCE字段,则结果应该为
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 13311.94 2011082910088 20110222 3639.42
20110401 110105196905107735 019570200006701 2010-01-29 3700.00 60.58 2011082910087 20110210 60.58
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 13311.94 2011082910088 20110222 9672.52
20110401 110105196905107735 019570200006801 2010-02-29 60000.00 60.58 2011082910087 20110210 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 13311.94 2011082910088 20110222 0
20110401 110105196905107735 019570200008301 2010-03-29 70000.00 60.58 2011082910087 20110210 0
20110401 420106197611114028 019570200021601 2010-05-27 996020.66 203.16 2011082910081 20110211 203.16CSDN上有大神能写出这个更新SQL语句吗?
我写了一个,但是更新的有问题,没有达到我要的效果。大神帮我看看,或者重写一个能达到我要求的语句。我的语句如下: --
declare
v_totall_m number; ----可分配金额
sydkje number; ----该笔剩余多少金额没抵扣
begin
for c1 in (select distinct idnum,productid from cdt_temp1) loop
select dkzje into v_totall_m from cdt_temp1 t where t.idnum = c1.idnum and t.productid = c1.productid and rownum <2;
for v_c in (select idnum,productid,lendid,balance from cdt_temp1 t where t.idnum = c1.idnum and t.productid = c1.productid order by dkffr asc) loop
---该笔剩余多少没抵扣
select t.balance-(select yjdkje from (select lendid,sum(t.sjdk_balance) yjdkje from cdt_temp1 t group by t.lendid) where lendid=v_c.lendid) into sydkje from cdt_temp1 t where lendid=v_c.lendid and rownum <2;
if v_totall_m - sydkje >= 0 then
update cdt_temp1 set sjdk_balance = v_c.balance where lendid = v_c.lendid and idnum = c1.idnum;
v_totall_m:= v_totall_m - v_c.balance;
else
update cdt_temp1 set sjdk_balance = v_totall_m where lendid = v_c.lendid and idnum = c1.idnum;
goto l1;
end if;
end loop;
<<l1>>
commit;
end loop;
end
要求在对金额进行分摊时需要注意如下原则:
1、按时间顺序先后来分摊,即按DKFFR优先从日期小的开始分摊
2、每个LENDID对应的分摊金额不能大于BALANCE
3、每个PRODUCTID对应的分摊金额汇总应该等于该PRODUCTID对应的DKZJE若按照这几个原则分摊,找不到满足的金额怎么办
比如PRODUCTID 为2011082910081 若他的BALANCE<203.16 则就没办法分摊了
这种情况怎么办,还有你给出建表的语句和测试数据
producId 为2011082910088 的两笔分摊金额怎么算出来的 为什么是
3639.42和9672.52 而不是3640和9671.94
------------------
描述的还是不够详细
建表语句
create table CDT_TEMP2
(
WORKDATE NUMBER(8) not null,
IDNUM VARCHAR2(50),
LENDID VARCHAR2(100) not null,
DKFFR VARCHAR2(100),
BALANCE NUMBER(21,2),
DKZJE NUMBER(21,2),
PRODUCTID NUMBER(13) not null,
TJDATE NUMBER(8),
SJDK_BALANCE NUMBER(21,2)
)
插入测试数据
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006701 ,'2010-01-29' ,7000.00 , 13311.94, 2011082910088, 20110209, 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006701 ,'2010-01-29' ,50.00, 60.58, 2011082910087, 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006801 ,'2010-02-28' ,300.00 , 13311.94, 2011082910088 , 20110209 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006801 ,'2010-02-28' ,260000.00, 60.58, 2011082910087, 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200008301 ,'2010-03-29' ,370000.00, 13311.94, 2011082910088 , 20110209 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200008301 ,'2010-03-29' ,370000.00, 60.58, 2011082910087 , 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,420106197611114028 ,019570200021601 ,'2010-05-27' ,996020.66, 203.16, 2011082910081, 20110123 , 0 ) ; 计算结果
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE ROWID
20110401 110105196905107735 19570200006701 2010-01-29 7000.00 13311.94 2011082910088 20110209 7000.00
20110401 110105196905107735 19570200006701 2010-01-29 50.00 60.58 2011082910087 20110210 0.00
20110401 110105196905107735 19570200006801 2010-02-28 300.00 13311.94 2011082910088 20110209 300.00
20110401 110105196905107735 19570200006801 2010-02-28 260000.00 60.58 2011082910087 20110210 0.00
20110401 110105196905107735 19570200008301 2010-03-29 370000.00 13311.94 2011082910088 20110209 6011.94
20110401 110105196905107735 19570200008301 2010-03-29 370000.00 60.58 2011082910087 20110210 60.58
20110401 420106197611114028 19570200021601 2010-05-27 996020.66 203.16 2011082910081 20110123 203.16
create table CDT_TEMP2
(
WORKDATE NUMBER(8) not null,
IDNUM VARCHAR2(50),
LENDID VARCHAR2(100) not null,
DKFFR VARCHAR2(100),
BALANCE NUMBER(21,2),
DKZJE NUMBER(21,2),
PRODUCTID NUMBER(13) not null,
TJDATE NUMBER(8),
SJDK_BALANCE NUMBER(21,2)
)
插入测试数据
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006701 ,'2010-01-29' ,7000.00 , 13311.94, 2011082910088, 20110209, 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006701 ,'2010-01-29' ,50.00, 60.58, 2011082910087, 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006801 ,'2010-02-28' ,300.00 , 13311.94, 2011082910088 , 20110209 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200006801 ,'2010-02-28' ,260000.00, 60.58, 2011082910087, 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200008301 ,'2010-03-29' ,370000.00, 13311.94, 2011082910088 , 20110209 , 0 ) ;
insert into cdt_temp2 values(20110401 ,110105196905107735 ,019570200008301 ,'2010-03-29' ,370000.00, 60.58, 2011082910087 , 20110210 , 0 ) ;
insert into cdt_temp2 values(20110401 ,420106197611114028 ,019570200021601 ,'2010-05-27' ,996020.66, 203.16, 2011082910081, 20110123 , 0 ) ; 计算结果
WORKDATE IDNUM LENDID DKFFR BALANCE DKZJE PRODUCTID TJDATE SJDK_BALANCE ROWID
20110401 110105196905107735 19570200006701 2010-01-29 7000.00 13311.94 2011082910088 20110209 7000.00
20110401 110105196905107735 19570200006701 2010-01-29 50.00 60.58 2011082910087 20110210 0.00
20110401 110105196905107735 19570200006801 2010-02-28 300.00 13311.94 2011082910088 20110209 300.00
20110401 110105196905107735 19570200006801 2010-02-28 260000.00 60.58 2011082910087 20110210 0.00
20110401 110105196905107735 19570200008301 2010-03-29 370000.00 13311.94 2011082910088 20110209 6011.94
20110401 110105196905107735 19570200008301 2010-03-29 370000.00 60.58 2011082910087 20110210 60.58
20110401 420106197611114028 19570200021601 2010-05-27 996020.66 203.16 2011082910081 20110123 203.16
字段解释:
同一IDNUM下可以有多个LENDID
DKFFR为日期字段
DKZJE为对应PRODUCTID对应的可分摊总金额
SJDK_BALANCE为每条记录最终得到的分摊金额,初始值都为0要求在对金额进行分摊时需要注意如下原则:
1、按PRODUCTID从小到大开始分摊,PRODUCTID有多条记录按对应TJDATE时间小的先分摊
2、分摊时还要对LEND对应的DKFFR日期从小到大开始分摊
3、每个LENDID对应的分摊金额不能大于BALANCE
4、每个LENDID对应的分摊金额汇总不能大于LENDID对应的最大BALANCE
5、每个PRODUCTID对应的分摊金额汇总应该等于该PRODUCTID对应的DKZJE