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

[浪尖赏花]

数据很简单:
max value
8 0
9 0
10 17
11 5
12 0

max值自8开始，逐行递增1
每行的value不允许超过max值，超过部分计入下行，以此类推
要求结果为
8 0
9 0
10 10
11 11
12 1


说明，不一定仅此五行，后面的可以按max规律再加

[luoyoumou]

-- 用ＳＱＬ很难，游标处理是最好的啦！

[njlywy]

坐等达人…

[真的不知道取什么名]

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

[gelyon]

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

[gelyon]

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

[浪尖赏花]

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

[tangren]

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

[浪尖赏花]

[Quote=引用 7 楼 tangren 的回复:]
SQL code
--试一下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,……
[/Quote]
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
)

[浪尖赏花]

修改了一下^_^

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方法呢

[minitoy]

你之前的统计不能变通下来实现需求么？

[tangren]

[Quote=引用 9 楼 lihui_shine 的回复:]

修改了一下^_^
SQL code
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,……
[/Quote]
优化得不错，我写复杂了哈~~~

[浪尖赏花]

[Quote=引用 10 楼 minitoy 的回复:]
你之前的统计不能变通下来实现需求么？
[/Quote]
我是做的年假统计，给我的数据，只有入职日期、休假的明细表。
年假嘛，大家都懂的，有时间限制的，过时不休视为自动放弃，我需要统计的是每个年周期已休记录数、可休天数。兄台有什么好的办法？

比如我是2008-12-01入职的，到2009-12-01开始我可以休8天，截止日期到2010-11-30日，
2009-12-01至2010-11-30，我有9天的年假，截止日期到2011-11-30日，
以此类推
除了第一年要满一年外外，以后每年的年假可休时间都是按照已过月份/12*年周期年假天数

[浪尖赏花]

今天晚上结贴，如果大家想挑战下，有新的方法的话，我可以另外开贴给分