select rownum,x,sum(decode(x,0,1,0)) flag, sum(sum(decode(x,0,1,0))) over (order by rownum,x rows between unbounded preceding and current row) sum_flag from t group by rownum,x自己琢磨去吧...
create table cm (id integer,g1 varchar2(20),g2 varchar2(20))insert into cm(id,g1,g2) select 1, '0,', '' from dual union all select 2, '0.1,', '' from dual union all select 3, '0.2,', '' from dual union all select 4, '0.3,', '' from dual union all select 5, '0,', '' from dual union all select 6, '0.2,', '' from dual union all select 7, '0.2,', '' from dual union all select 8, '0.1,', '' from dual union all select 9, '0,', '' from dual union all select 10, '0.3', '' from dual union all select 11, '0.1', '' from dual declare cursor ap is select id,g1 from cm; sg1 varchar(20); sg2 integer; iid integer; begin open ap; sg2:=0; loop fetch ap into iid,sg1; exit when ap%NOTFOUND; begin if sg1='0,' then sg2:=sg2+1; end if; update cm set g2=to_char(sg2) where id=iid; end; end loop; close ap; end; select * from cm; ID G1 G2 ---------- -------------------- -------------------- 1 0, 1 2 0.1, 1 3 0.2, 1 4 0.3, 1 5 0, 2 6 0.2, 2 7 0.2, 2 8 0.1, 2 9 0, 3 10 0.3 3 11 0.1 3已选择11行。
sum(sum(decode(x,0,1,0))) over (order by rownum,x rows between
unbounded preceding and current row) sum_flag from t
group by rownum,x自己琢磨去吧...
create table cm
(id integer,g1 varchar2(20),g2 varchar2(20))insert into cm(id,g1,g2)
select 1, '0,', '' from dual
union all
select 2, '0.1,', '' from dual
union all
select 3, '0.2,', '' from dual
union all
select 4, '0.3,', '' from dual
union all
select 5, '0,', '' from dual
union all
select 6, '0.2,', '' from dual
union all
select 7, '0.2,', '' from dual
union all
select 8, '0.1,', '' from dual
union all
select 9, '0,', '' from dual
union all
select 10, '0.3', '' from dual
union all
select 11, '0.1', '' from dual
declare
cursor ap is
select id,g1 from cm;
sg1 varchar(20);
sg2 integer;
iid integer;
begin
open ap;
sg2:=0;
loop
fetch ap into iid,sg1;
exit when ap%NOTFOUND;
begin
if sg1='0,' then
sg2:=sg2+1;
end if;
update cm
set g2=to_char(sg2)
where id=iid;
end;
end loop;
close ap;
end;
select * from cm; ID G1 G2
---------- -------------------- --------------------
1 0, 1
2 0.1, 1
3 0.2, 1
4 0.3, 1
5 0, 2
6 0.2, 2
7 0.2, 2
8 0.1, 2
9 0, 3
10 0.3 3
11 0.1 3已选择11行。