最简单,最快的更新,但和你的要求有一点不同,你看看能行不 create sequence s; update 表a set pnum=s.nextval; drop sequence s;
上面只按要求给出了select的结果,如果要更新表中数据,可以create table tmp_table1 as select fileID,row_number() over (partition by filedID order by PNUM) as PNUM from table1drop table table1;alter table tmp_table1 rename to table1;删除重建即可,呵呵
加一张临时表tmp_table3 先将字段page_id file_id page_num archives_id放在这张表中 利用create table tmp_table3 as select page_id,file_id,archives_id,row_number() over (partition by archives_id order by page_num) as page_num from tmp_table2 where archives_id is not null 更新page_num 然后 通过page_id 更新table1中的page_num
create sequence s;
update 表a set pnum=s.nextval;
drop sequence s;
select fileID,row_number() over (partition by filedID order by PNUM) as PNUM from table1drop table table1;alter table tmp_table1 rename to table1;删除重建即可,呵呵
我写过一些类似的过程,总觉得效率没SQL高。不知道是不是我的过程写的不好谢谢啦:)
谢谢各位热情留言!
NinGoo(宁哥)的方法是可以的
--example
SQL> SELECT ST_ID,USEUNITAREA FROM BAO_TEMP
2 /ST_ID USEUNITAREA
------ -----------
DF1 .781716
ML 54.234482
ML 1554.11209
HA 130.426364
DF1 22.60081
DF2 .614142
HA 106.768012
DF2 8.051901
DF1 1.69538
SM 257.235272
ML 14.470944ST_ID USEUNITAREA
------ -----------
ML 534.273982
DF1 31.503908
DF2 92.205738
DF1 2.255017
CT 563.142254
CT 1066.09791
CT 9800.84679
CT 1559.2081選取了 19 列
------------------------------------------------------------------------
SQL> SELECT ST_ID,row_number() OVER (PARTITION BY ST_ID ORDER BY USEUNITAREA) AREA
2 FROM BAO_TEMP
3 /ST_ID AREA
------ ----------
CT 1
CT 2
CT 3
CT 4
DF1 1
DF1 2
DF1 3
DF1 4
DF1 5
DF2 1
DF2 2ST_ID AREA
------ ----------
DF2 3
HA 1
HA 2
ML 1
ML 2
ML 3
ML 4
SM 1選取了 19 列
select fileID,row_number() over (partition by fileID order by PNUM) as PNUM from table1 谢谢NinGoo(宁哥) !
还有一个问题:如果涉及到另一张表table2 有字段ArchID. table2 和 table1通过fileID 关联table2 中fileID 为主键 可能有ArchID相对应 或者(ArchID为空)没有对应. 且table2 中
ArchID可以重复:一个ArchID可以包含若干个fileID . table2 和 table1 关联起来 一个ArchID下 的fileID 所对应的PNUM都不能出现重复... SQL语句怎样完善? 谢谢!
page_id file_id page_num file_id archives_id
47777 434735 1 434735 382322
47778 434735 1 434736 382322
47779 434736 1 434737 382322
47780 434736 1 434738 382323
47781 434737 2 434739
47782 434737 2 ... ...
47783 434738 2
47784 434738 2
47785 434738 1
47786 434739 1
... ... ...想得到的结果为:
page_id file_id page_num archives_id47777 434735 1 382322
47778 434735 2 382322
47779 434736 3 382322
47780 434736 4 382322
47781 434737 5 382322
47782 434737 6 382322
47783 434738 1 382323
47784 434738 2 382323
47785 434738 3 382323
47786 434739 1
... ... ...
主要是page_num的变化,而且archives_id在另一张表中.
利用create table tmp_table3 as
select page_id,file_id,archives_id,row_number() over (partition by archives_id order by page_num) as page_num from tmp_table2 where archives_id is not null 更新page_num 然后 通过page_id 更新table1中的page_num