SQL语句如何实现按递增赋值

[justju]

table1

n1 n2
--------
a
b
e
r

实现

n1 n2
--------
a z0001
b z0002
e z0003
r z0004

[fxianxian]

SQL> select * from table1;

N1 N2
-- -----
A
B
E
R
create or replace procedure ppppp is
2 v_rn int:=0;
3 begin
4 for c1 in (select n1 from table1 order by n1 ) loop
5 v_rn:=v_rn+1;
6 update table1
7 set n2='z'||lpad(v_rn,3,'0')
8 where n1=c1.n1;
9 end loop;
10 commit;
11 end;
12
13 /

Procedure created
execute ppppp;

PL/SQL procedure successfully completed

SQL> select * from table1;

N1 N2
-- -----
A z001
B z002
E z003
R z004

[kinglht]

-- Create sequence
create sequence seq_test
minvalue 1
maxvalue 99999999999999999
start with 1
increment by 1;

create table t_test as
select n1,to_char(seq_test.nextval,'fm0000') n2
from table1;

drop table1;
rename t_test table1;

ok

[sangxiao]

把N2分成两个字段好了 n21 n22
n1 n21 n22
----------------------
a z 0001
b z 0002
e z 0003
r z 0004

[bugchen888]

BEGIN
FOR c IN
	(SELECT ROWID AS rid,
		Row_Number() over (ORDER BY n1) AS rn
	FROM table1)
LOOP
	UPDATE table1
	SET n21='z',
		n22=SubStr('0000'||To_Char(c.rn),-4)
	WHERE ROWID=c.rid;
END LOOP;
COMMIT;
END;
/

[ouxuan000]

--无敌小脚本
create table temp as select * from talbe1;
delete table temp;
insert into temp select ta.n1,SubStr('0000'||To_Char(ta.a),-4)
from (select row_number() over(order by n1) a,t.* from table1 t) ta;
drop tabel table1;
create table table1 as select * from temp;
drop table temp;

[diandian09]

bugchen888 是个能人啊。。。。葱白~