超有难度的sql，200分奉上！

Question

数据很简单:
max  value
8    0
9    0
10   17
11   5
12   0max值自8开始，逐行递增1
每行的value不允许超过max值，超过部分计入下行，以此类推
要求结果为
8    0
9    0
10   10
11   11
12   1
说明，不一定仅此五行，后面的可以按max规律再加

Answer 1

/*
抛砖引玉吧
缺陷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

Answer 2

--初始数据：
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

Answer 3

--再修改下，假设你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    --新增的分摊数据

Answer 4

感谢zhuomingwang、gelyon大大的回复，用存储过程、匿名块等方式的确可以解决我们这个问题！
但是有一个问题，我的数据源不是直接从表里的读取出来的，数据源本身就是统计出来的，用sql的方式能否实现。如果实在实现不了，我该如何解决？

Answer 5

--试一下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
    )
) 

Answer 6

tangren大大厉害，基本上效果达到了，我刚刚测试了，基本上OK，有一组数据有点问题，tangren大大是不是有个别情况没有考虑到呢^_^
我先来消化消化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
)

Answer 7

修改了一下^_^
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
    )
)
还有没有其他sql方法呢