3,490
社区成员
发帖
与我相关
我的任务
分享
select max,case when tmp>max then max else tmp end val from (
select max,v,tmp from
(select max,val val from t order by max)
model
dimension by (max)
measures (val v,max m,0 as tmp)
rules(
tmp[any]= case when v[cv()-1] is null then
v[cv()]
else
case when tmp[cv()-1]>m[cv()-1] then
tmp[cv()-1]-m[cv()-1]+v[cv()]
else
v[cv()]
end
end
)
)
with t as (
select 8 max,0 val from dual
union all
select 9,0 from dual
union all
select 10,17 from dual
union all
select 11,12 from dual
union all
select 12,0 from dual
union all
select 13,18 from dual
union all
select 14,0 from dual
)
--试一下SQL解决,表名为t
select max,decode(tmp1,-1,tmp,max) val from (
select max,v,tmp,tmp1 from
(select max,val val from t order by max)
model
dimension by (max)
measures (val v,max m,0 as tmp,0 as tmp1)
rules(
tmp[any]= case when v[cv()-1] is null then
case when v[cv()]>m[cv()] then
v[cv()]-m[cv()]
else
v[cv()]
end
else
case when tmp[cv()-1]+v[cv()]>m[cv()] then
tmp[cv()-1]+v[cv()]-m[cv()]
else
tmp[cv()-1]+v[cv()]
end
end,
tmp1[any]= case when v[cv()-1] is null then
case when v[cv()]>m[cv()] then 1 else -1 end
else
case when tmp[cv()-1]+v[cv()]>m[cv()] then 1 else -1 end
end
)
)
--再修改下,假设你value值很大,导致你max没有那么多需要分摊,自动新增:
--原数据:
--注意没有max=16的
select * from tmp;
MAX Value
-------------
8 0
9 0
10 17
11 5
12 0
13 26
14 13
15 12
--执行如下匿名块:
declare
cnt number;
val number;
begin
select count(*) into cnt from tmp where value>max;
while cnt>0 loop
for rs in (select * from tmp where value>max ) loop
val := rs.value-rs.max;
update tmp set value=max where max=rs.max;
update tmp set value=value+val where max=rs.max+1;
if sql%rowcount!=1 then
insert into tmp(max,value) values(rs.max+1,val); --新增
end if;
commit;
end loop;
select count(*) into cnt from tmp where value>max;
end loop;
end;
--结果:
select * from tmp;
MAX Value
-----------------
8 0
9 0
10 10
11 11
12 1
13 13
14 14
15 15
16 9 --新增的分摊数据
--初始数据:
select * from tmp;
MAX Value
------------------
8 0
9 0
10 17
11 5
12 0
13 26
14 13
15 12
16 3
--执行如下匿名PLSQL块:
declare
cnt number;
val number;
begin
select count(*) into cnt from tmp where value>max;
while cnt>0 loop
for rs in (select * from tmp where value>max ) loop
val := rs.value-rs.max;
update tmp set value=max where max=rs.max;
update tmp set value=value+val where max=rs.max+1;
commit;
end loop;
select count(*) into cnt from tmp where value>max;
end loop;
end;
--结果:
select * from tmp;
MAX Value
-----------------
8 0
9 0
10 10
11 11
12 1
13 13
14 14
15 15
16 12
/*
抛砖引玉吧
缺陷m列(即你的max列只能是从8开始按1递增
楼主执行这个过程前做好备份
*/
create or replace procedure scott.update_pro
as
n_flag number;
n_cnt number;
n_m test.m%type;
n_v test.v%type;
begin
n_flag:=0;
select count(*) into n_cnt from test;
for i in 8..7+n_cnt loop
select m,v into n_m,n_v from test where m=i;
if n_v+n_flag<=n_m then
update test set v=n_v+n_flag
where m=i;
else
update test set v=m
where m=i;
n_flag:=n_v+n_flag-n_m;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
--执行过程前
select * from test;
M V
------ ----------
8 0
9 0
10 17
11 5
12 0
--执行过程后
select * from test;
M V
---------- ----------
8 0
9 0
10 10
11 11
12 1