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

Question

http://topic.csdn.net/u/20110309/19/04318576-4930-4b57-a083-0e8368666436.html

Answer 1

---用sql 是不好搞，不用循环是不好搞
SQL> select * from tb2;
 
       MAX      VALUE
---------- ----------
         8          0
         9          0
        10         17
        11          5
        12          0
 
SQL> create or replace type connect_type as object(max number,value number);
  2  /
 
Type created
 
SQL> create or replace type connect_type_tab is table of connect_type;
  2  /
 
Type created
SQL> create or replace function f_connect_type_tab return connect_type_tab
  2  as
  3  cnt number:=0;
  4  connect_type_tb connect_type_tab;
  5  begin
  6    select connect_type(max,value) bulk collect into connect_type_tb from tb2 where max>7;
  7    for i in 1..connect_type_tb.count
  8      loop
  9      if   connect_type_tb(i).max<connect_type_tb(i).value+cnt then
 10         cnt:=connect_type_tb(i).value-connect_type_tb(i).max+cnt;
 11        connect_type_tb(i).value:=connect_type_tb(i).max;
 12        else
 13          connect_type_tb(i).value:=cnt+connect_type_tb(i).value;
 14        end if;
 15        end loop;
 16  
 17        return connect_type_tb;
 18  end;
 19  /
 
Function created
 
SQL> select * from table(f_connect_type_tab)
  2  /
 
       MAX      VALUE
---------- ----------
         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

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

Answer 4

如果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

我比较认同wkc168的思路，写自定义函数是比较好的解决方案，我再想想具体实现。

Answer 7

唐人的SQL MODEL 子句不错，但需要OLAP技术支持，学习下。

Answer 8

修改了一下^_^
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
    )
)

Answer 9

create or replace type connect_type as object(max number,value number);
create or replace type connect_type_tab is table of connect_type;CREATE OR REPLACE FUNCTION func_get_re_assign(i_cur SYS_REFCURSOR)
  RETURN connect_type_tab
  PIPELINED AS
  v_max   NUMBER;
  v_value NUMBER;
  v_bin   NUMBER := 0;
BEGIN
  LOOP
    FETCH i_cur
      INTO v_max, v_value;
    EXIT WHEN i_cur%NOTFOUND;
    IF v_bin + v_value >= v_max THEN
      v_bin := v_bin + v_value - v_max;
      PIPE ROW(connect_type(v_max, v_max));
    ELSE
      PIPE ROW(connect_type(v_max, v_bin + v_value));
      v_bin := 0;
    END IF;
  END LOOP;
  CLOSE i_cur;
  RETURN;
END;SQL> SELECT *
  2    FROM TABLE(func_get_re_assign(CURSOR (SELECT MAX, VALUE
  3                                     FROM t_test
  4                                    ORDER BY MAX ASC)));
 
       MAX      VALUE
---------- ----------
         8          0
         9          0
        10         10
        11         11
        12          1
 
SQL> 

Answer 10

这样你只要把你得到结果的sql语句放到 SELECT MAX, VALUE FROM t_test ORDER BY MAX ASC的位置就可以了

Answer 11

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
    )
)

Answer 12

想用唐人的方法来着，无奈线上库是oracle9的最后用的wkc168方法，总算是解决了吧，结贴了