有一个表SP_VOYAGE_PORT_END,里面有两个字段
VOYAGE_ID(可以重复)、SEQUENCE(序号列)
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
bbbbb 3
bbbbb 3
bbbbb 3
bbbbb 3
ccccc 3
ccccc 3
请问我应该如何对SEQUENCE列排列成:
VOYAGE_ID(可以重复)、SEQUENCE(序号列)
aaaaa 1
aaaaa 2
aaaaa 3
aaaaa 4
aaaaa 5
aaaaa 6
bbbbb 1
bbbbb 2
bbbbb 3
bbbbb 4
ccccc 1
ccccc 2 需求思路谢谢大家了!!!!!!!!!!!
VOYAGE_ID(可以重复)、SEQUENCE(序号列)
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
aaaaa 3
bbbbb 3
bbbbb 3
bbbbb 3
bbbbb 3
ccccc 3
ccccc 3
请问我应该如何对SEQUENCE列排列成:
VOYAGE_ID(可以重复)、SEQUENCE(序号列)
aaaaa 1
aaaaa 2
aaaaa 3
aaaaa 4
aaaaa 5
aaaaa 6
bbbbb 1
bbbbb 2
bbbbb 3
bbbbb 4
ccccc 1
ccccc 2 需求思路谢谢大家了!!!!!!!!!!!
我是要UPDATE到数据库中,也就是在表中的数据重新排序一下
l_rn number(2);
l_name varchar2(20);
l_id number;
cursor a is
select name,id,row_number() over(partition by name order by name) rn
from bb
for update of bb.id;
begin
open a;
loop
fetch a into l_name,l_id,l_rn;
exit when a%notfound;
update bb set id=l_rn where current of a;
end loop;
close a;
end;
/SQL> select * from bbNAME ID
---------- ----------
aaaa 3
aaaa 3
aaaa 3
aaaa 3
bbbb 3
bbbb 3
bbbb 3
bbbb 3已选择8行。SQL> exec u_a;PL/SQL 过程已成功完成SQL> select * from bbNAME ID
---------- ----------
aaaa 1
aaaa 2
aaaa 3
aaaa 4
bbbb 1
bbbb 2
bbbb 3
bbbb 4已选择8行。