如何按照一定的金额提取记录

[dbcz444]

有一万多条记录，就是号码对应所欠的金额，需要按照所欠金额分成若干的表。
比如，按照所欠金额不超过-1000元来提取记录，即，按照记录顺序，从第一条开始统计，直至一定量的号码所欠金额不超过-1000元就归为一个表；接着从下一条记录开始统计，直至一定量的号码所欠金额不超过-1000元就归为另一个表……表的格式如下：

号码 所欠金额
899610011 -2.6
899685120 -589.55
852169845 -355.21
896517532 -63.59
965328145 -65.78
851269874 -75.41
695847266 -99.56
321589648 -265.65
585648952 -6.32
256987456 -486.25
236589455 -142.66
658921547 -65.32
698541239 -52.86
689532156 -3.07
………………

依据条件，把
899610011 -2.6
899685120 -589.55
852169845 -355.21
生成一个表1，接着从下一条记录开始，
896517532 -63.59
965328145 -65.78
851269874 -75.41
695847266 -99.56
321589648 -265.65
585648952 -6.32
这几个记录又生成一个表2，接着从下一条记录开始，
256987456 -486.25
236589455 -142.66
658921547 -65.32
698541239 -52.86
689532156 -3.07
这几个记录又生成一个表3……

如果可以按照随机提取不超-1000元的记录分成不同的表也可以了，谢谢。

[dbcz444]

求大神……

[nayi_224]

create table test_190319(ord number, money number);

select 1, 100 from dual union all
select 2, 800 from dual union all
select 3, 200 from dual union all
select 4, 100 from dual union all
select 5, 2000 from dual union all
select 6, 500 from dual 
;

insert into test_190319
select 1, 100 from dual union all
select 2, 800 from dual union all
select 3, 200 from dual union all
select 4, 100 from dual union all
select 5, 2000 from dual union all
select 6, 500 from dual ;

create table test_190319_temp as
select*from test_190319 
model 
dimension by(ord)
measures(money, 1 t_id, 0 smm)
(
smm[ord] = case when nvl(smm[cv() - 1], 0) + money[cv()] >= 1000 then 0 else nvl(smm[cv() - 1], 0) + money[cv()] end,
t_id[ord] = case when smm[cv() - 1] = 0 then nvl(t_id[cv() - 1], 1) + 1 else nvl(t_id[cv() - 1], 1) end
)
;

select*from test_190319_temp;

declare
i int := 0;
x int;
begin
  select max(t_id) into x from test_190319_temp;
  for i in 1..x loop
    execute immediate 'create table test_m_' || i || ' as select ord, money from test_190319_temp where t_id = ''' || i || '''';
  end loop;
end;

select*from test_m_1;
select*from test_m_2;
select*from test_m_3;

drop table test_m_1;
drop table test_m_2;
drop table test_m_3;

[nayi_224]

差了一点，换成这个

dimension by(row_number() over(order by ord) ord)

[nayi_224]

实在不行就换java写吧，半天怎么也写出来了。

[nayi_224]

给你换个简单的写法吧（其实就是把2楼的重新翻译一遍）

declare
i int := 0;
x int := 1;
begin
  execute immediate 'create table test_m_' || x || '(ord number, money number)';
  for cur in (select t1.ord, t1.money from test_190319 t1 order by t1.ord) loop
    i := i + cur.money;
    if i >= 1000 then
       i := 0;
       x := x + 1;
       execute immediate 'create table test_m_' || x || '(ord number, money number)';
    end if;
    execute immediate 'insert into test_m_' || x || ' values(' || cur.ord || ', ' || cur.money || ')';
  end loop;
end;

[dbcz444]

这几天一直在试，试不出来……水平低就是没有办法。

[dbcz444]

select * from test_190319_temp;

您的表中，select*from test_m_1和select*from test_m_2明显超过了1000……

[dbcz444]

smm[ord] = case when nvl(smm[cv() - 1], 0) + money[cv()] >= 1000 then 0 else nvl(smm[cv() - 1], 0) + money[cv()] end,
t_id[ord] = case when smm[cv() - 1] = 0 then nvl(t_id[cv() - 1], 1) + 1 else nvl(t_id[cv() - 1], 1) end

这里之中，如果SMM为0，T_ID就计算给下一个表，怎么改？谢谢。

[nayi_224]

主要是有些数据一条就已经超过了1000，把这事给忘了

select*from test_190319 
model 
dimension by(ord)
measures(money, 1 t_id, 0 smm, 0 flag)
(
flag[ord] = money[cv()],
smm[ord] = case when decode(nvl(smm[cv() - 1], 0), 0, flag[cv() - 1], nvl(smm[cv() - 1], 0)) + money[cv()] >= 1000 then 0 else nvl(smm[cv() - 1], 0) + money[cv()] end,
t_id[ord] = case when smm[cv() ] = 0 then nvl(t_id[cv() - 1], 1) + 1 else nvl(t_id[cv() - 1], 1) end
)
;

[nayi_224]

还是建议用6楼的写法，改个字就行

declare
i int := 0;
x int := 1;
begin
  execute immediate 'create table test_m_' || x || '(ord number, money number)';
  for cur in (select t1.ord, t1.money from test_190319 t1 order by t1.ord) loop
    i := i + cur.money;
    if i >= 1000 then
       i := cur.money;
       x := x + 1;
       execute immediate 'create table test_m_' || x || '(ord number, money number)';
    end if;
    execute immediate 'insert into test_m_' || x || ' values(' || cur.ord || ', ' || cur.money || ')';
    commit;
  end loop;
end;

[dbcz444]

非常感谢！！！！！！！！！！！！