老兄,用这个存储过程,就可以搞定,如果可以,能够执行就给点分,我希望得专家分create procedure fl as begin DECLARE CURSOR temp_cursor IS SELECT distinct a 条件 FROM tablename ORDER BY a; curRecord1 temp_cursor%ROWTYPE; begin FOR curRecord1 IN temp_cursor LOOP declare I number(4); cursor temp_dd is select rowid from tablename where a=curRecord1.条件; currecord2 temp_dd%rowtype; begin i:=1; for currecord2 in temp_dd loop update tablename set b=i where rowid=curRecord2.rowid; i:=i+1; end loop; end; END LOOP; end
给你一个用sql的。但要你的oracle版本在8.1.6以上。 假设表test(id,value), insert into test(id) value(1); insert into test(id) value(1); insert into test(id) value(1); insert into test(id) value(2); insert into test(id) value(2); insert into test(id) value(2); insert into test(id) value(2); ....update test a set a.value=(select value from (select id,row_number() over(partition by id order by id) as value from test) b where a.rowid=b.rowid)看看结果
SQL> select * from t_rank; A B ---------- ---------- 1 1 1 2 2 2 3 3 39 rows selected.SQL> update t_rank x 2 set b = (select count(*) from t_rank 3 where a = x.a 4 and rowid <= x.rowid);9 rows updated.SQL> select * from t_rank; A B ---------- ---------- 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3 9 rows selected.
as
begin
DECLARE
CURSOR temp_cursor IS SELECT distinct a 条件 FROM tablename ORDER BY a;
curRecord1 temp_cursor%ROWTYPE;
begin
FOR curRecord1 IN temp_cursor LOOP
declare
I number(4);
cursor temp_dd is select rowid from tablename where a=curRecord1.条件;
currecord2 temp_dd%rowtype;
begin
i:=1;
for currecord2 in temp_dd loop
update tablename set b=i where rowid=curRecord2.rowid;
i:=i+1;
end loop;
end;
END LOOP;
end
假设表test(id,value),
insert into test(id) value(1);
insert into test(id) value(1);
insert into test(id) value(1);
insert into test(id) value(2);
insert into test(id) value(2);
insert into test(id) value(2);
insert into test(id) value(2);
....update test a set a.value=(select value from (select id,row_number() over(partition by id order by id) as value from test) b where a.rowid=b.rowid)看看结果
A B
---------- ----------
1
1
1
2
2
2
3
3
39 rows selected.SQL> update t_rank x
2 set b = (select count(*) from t_rank
3 where a = x.a
4 and rowid <= x.rowid);9 rows updated.SQL> select * from t_rank;
A B
---------- ----------
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
9 rows selected.