超难sql语句，有人会写吗！

有表A，数据如下： 
 id                accno       date          rn      data1    data2       data3     
111111111111111111  aaaaaa      2010-4-26   1      500      800               
111111111111111111   bbbbbb      2010-5-7   2      1000     800              
222222222222222222   dddddd      2010-1-14   1      1000     600              
222222222222222222   gggggg      2010-2-7        2      2000     600              
222222222222222222   hhhhhh      2010-2-24       3      3000     600              
333333333333333333  nnnnnn      2010-3-14       1      500      3000              
333333333333333333  mmmmmm      2010-4-11       2      1000     3000              
333333333333333333  kkkkkk      2010-1-4        3      2000     3000              
333333333333333333  tttttt      2010-5-14       4      4000 3000              
333333333333333333  ffffff      2010-5-24       5      8000     3000              
字段解释：
同一id下可以有多个accno   
rn字段为对相同id按字段date时间排序
data1为日期对应金额
date2为该ID对应的可分摊金额
date3为每个accno实际得到的分摊金额

要求在对金额进行分摊时需要注意如下原则：
1、按时间顺序先后来分摊，即优先从日期小的开始分摊
2、每个accno对应的分摊金额不能大于data1
3、每个id对应的分摊金额汇总应该等于该ID对应的dat2
用sql语句分摊完金额，更新至data3字段，则结果应该为

 id                accno       date          rn      data1    data2       data3     
111111111111111111  aaaaaa      2010-4-26   1      500      800        500       
111111111111111111   bbbbbb      2010-5-7   2      1000     800        300      
222222222222222222   dddddd      2010-1-14   1      1000     600        600      
222222222222222222   gggggg      2010-2-7        2      2000     600        0      
222222222222222222   hhhhhh      2010-2-24       3      3000     600        0      
333333333333333333  nnnnnn      2010-3-14       1      500      3000       500       
333333333333333333  mmmmmm      2010-4-11       2      1000     3000       1000       
333333333333333333  kkkkkk      2010-1-4        3      2000     3000       1500       
333333333333333333  tttttt      2010-5-14       4      4000 3000        0      
333333333333333333  ffffff      2010-5-24       5      8000     3000        0

请问我的sql语句应该怎么写！！！！！请大虾指教！！！！

18 个解决方案

#1

分太少了,不干

#2

楼主，不太明白“分摊”是什么意思。
不过，看数据好象明白了。
不过，真得挺复杂。
还是写存储过程来实现比较合适，在存储过程里用Cursor循环取出每个ID对应的记录。针对每条记录做计算。

#3

引用 1 楼 wang123kui 的回复:

分太少了,不干

咱就这么多分了～谁会写啊

#4

declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data1 from t into v_totall_m;
    for v_c in (select accno,data2 from t where t.id = c1.id order by date asc) loop
        if v_totall_m - v_c.data2 > 0 then
          update t set data3 = v_c.data2 where accno = v_c.accno and id = c1.id;
          v_totall_m:= v_totall_m - v_c.data2;
        else
          update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
          goto l1;
        end if;    
    end loop;
    <<l1>>
    commit;
end loop;
end;

为了赚点技术分不容易啊,哎

#5

declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data2 from t into v_totall_m;
    for v_c in (select accno,data1 from t where t.id = c1.id order by date asc) loop
        if v_totall_m - v_c.data1 > 0 then
          update t set data3 = v_c.data1 where accno = v_c.accno and id = c1.id;
          v_totall_m:= v_totall_m - v_c.data1;
        else
          update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
          goto l1;
        end if;    
    end loop;
    <<l1>>
    commit;
end loop;
end;

上面好像列名写反了

#6

引用 5 楼 wang123kui 的回复:

SQL code
declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data2 from t into v_totall_m;
    for v_c in (select accno,data1 from t where t.id = c1……

不行啊，报错了。黄岛主救命啊。
第5行 select data1 from t into v_totall_m;   的into报错：命令未正确结束。

#7

 

select data1 into v_totall_m from t ;

哥哥,这样的错误你也看不出来啊

#8

select data1 into v_totall_m from t where t.id = c1.id and rownum <2;


哎,仔细一看错误还真不少

#9

declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data1 into v_totall_m from t where t.id = c1.id and rownum <2;
    for v_c in (select accno,data1 from t where t.id = c1.id order by date asc) loop
        if v_totall_m - v_c.data1 > 0 then
          update t set data3 = v_c.data1 where accno = v_c.accno and id = c1.id;
          v_totall_m:= v_totall_m - v_c.data1;
        else
          update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
          goto l1;
        end if;    
    end loop;
    <<l1>>
    commit;
end loop;
end;

还是发个完整的吧

#10

引用 8 楼 wang123kui 的回复:

select data1 into v_totall_m from t where t.id = c1.id and rownum <2;


哎,仔细一看错误还真不少

恩，应该是select data2 into v_totall_m from t where t.id = c1.id and rownum <2;
执行没报错了。我套到我的SQL里执行试试。非常感谢

#11

declare
n_money number;  --可分配金额
s_accno char(6);
n_data1 number;
n_data2 number;
n_data3 number;
cursor c2(n_id number)  is select accno,data1,data2 from a where id=n_id  order by date;
begin
  for c1 in (select distinct id from a) loop
      n_money:=0;
      n_data3:=0;
      open c2; 
        loop
          fetch c2 into s_accno,nvl(n_data1,0),nvl(n_data2,0);
          exit when c2%notfound;
          if c2%rowcount=1 then
             n_money:=n_data2-n_data1;
             if n_money<0 then 
                n_data3:=n_data2;
             else
                n_data3:=n_data2-n_data1;
             end if;
          else
            if n_money< 0 then
               n_data3:=0;
            else
               if n_money<n_data1 then
                  n_data3:=n_money;
               else
                  n_data3:=n_money-n_data1
               end if;
            end if ;           
          end if ;
          update a set data3 = n_data3 where accno = s_accno and id = c1.id; 
        end loop;
      close c2;
  end loop;
  commit;
end;

#12

引用 9 楼 wang123kui 的回复:

SQL code
declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data1 into v_totall_m from t where t.id = c1.id and rownum <2;
    for v_c in (select a……

黄岛主十分强大啊，能稍微注释一下吗？要求会不会有点过啊 －。－

#13

引用 11 楼 nvhaixx 的回复:

declare
n_money number; --可分配金额
s_accno char(6);
n_data1 number;
n_data2 number;
n_data3 number;
cursor c2(n_id number) is select accno,data1,data2 from a where id=n_id order by date;
begin
  ……

纠正一下open c2（c1.id）; 

#14

大哥，这纯粹体力活啊！

#15

引用 12 楼 hc860102 的回复:

引用 9 楼 wang123kui 的回复:
SQL code
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data1 into v_totall_m from t where t.id = c1.id and rownum <2;
for v……

逻辑并不复杂,静下心来你是能看明白的

#16

引用 15 楼 wang123kui 的回复:

引用 12 楼 hc860102 的回复:
引用 9 楼 wang123kui 的回复:
SQL code
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data1 into v_totall_m from t where t.id = c1.i……

恩，前面都看明白了 ，逻辑比较清晰。loop循环以前用过看的懂
但是goto l1 是什么意思，以前没用过goto。最后<<l1>>  commit;这个<<l1>> 是起什么作用呢。

#17

引用 16 楼 hc860102 的回复:

引用 15 楼 wang123kui 的回复:
引用 12 楼 hc860102 的回复:
引用 9 楼 wang123kui 的回复:
SQL code
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data1 into v_totall_m……

GOTO的作用是跳出第一个FOR循环 ,
没用过GOTO,可以到网上搜一下GOTO的用法,这个用法一般不建议使用,
会破坏程序结构的完整性.

#18

declare
v_totall_m number;  ----可分配金额
begin
for c1 in (select distinct id from t) loop
    select data1 from t into v_totall_m;
    for v_c in (select accno,data2 from t where t.id = c1.id order by date asc) loop
        if v_totall_m - v_c.data2 > 0 then
          update t set data3 = v_c.data2 where accno = v_c.accno and id = c1.id;
          v_totall_m:= v_totall_m - v_c.data2;
        else
          update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
          goto l1;
        end if;    
    end loop;
    <<l1>>
    commit;
end loop;
end;