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

/*

抛砖引玉吧

缺陷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

--初始数据：

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

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

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

    )

) 

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

)

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

    )

)