我想给一个表加一列id,想通过rownum实现,id的顺序和表中一列的顺序还要一致.

Question

你的ID用来做什么？用SEQUENCE不行吗？

Answer 1

您的语句在sqlserver中应该没问题,在oracle中应该也不会有问题的. 

Answer 2

1.
将表的rowid copy到你的id列(id varchar2(64))
update tablename set id = rowid;2.
将表的rownum copy到你的id列(id number(30))
update tablename set id = rownum;

Answer 3

可以转换成排座次问题，我的做法如下：
update tablename
set id=t1.forder 
from tablename ,(select t2.b as b,count(t3.b) as forder from tablename t2,tablename t3
where t2.b<=t3.b
group by t2.b
)t1
where tablename.b=t1.b数据量大时可能较慢，但可以解决问题。

Answer 4

update tablename set id =sequencename.nextval;

Answer 5

这个不行的，顺序可能不对，我再想想。clwyf(芯片)的方法我试了一下，好像可行，没发现问题。

Answer 6

clwyf(芯片)的办法可以update rownum，但是不能排序吧？特别是当表中的纪录有过删除的时候。update tablename 
   set id = (select rownum 
               from (select rownum, tablename
                      order by b desc) d
              where d.a=tablename.a)
这样子在我这边不能运行，因为在子查询里不能带order by。不知道你们的版本行不行。如果实在不行，最方便的用法还不如用临时表（a 为主键）：
insert into temptable (id, a)
select rownum, a
  from tablename;update tablename x
   set id = (select id from temptable a=x.a);其实还是用临时表效率最高。

Answer 7

对不起，前面insert的时候少了排序子句，而insert中又不能用order by，还是不行。实在不行的话，用updatable cursor吧。

Answer 8

"updatable cursor"   怎么用?

Answer 9

1、先创一个sequence
  create sequence seq_id
   start with 1
   increment by 1;
2、如KingSunSha(弱水三千)所说，先创一个临时表
  create table temptable as select id,rid from ( select seq_id.nextval id,rowid rid from tablename order by column);
3.update tablename set id=(select id from temptable where temptable.rid=tablename=rowid);
4.drop seq_id、temptable此法虽笨，也许可以满足你的要求 
 

Answer 10

3.update tablename set id=(select id from temptable where temptable.rid=tablename.rowid);
3与4不能合并为一

Answer 11

To feng_zi(风子):
3. 我还是没法在CREATE TABLE或者INSERT INTO的子查询中用ORDER BY子句.TO gogoli(狗狗): UPDATABLE CURSOR的用法示例
    DECLARE 
        CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE; 
        ... 
    BEGIN 
        OPEN c1; 
        LOOP             FETCH c1 INTO ... 
            ... 
            UPDATE emp SET sal = new_sal WHERE CURRENT OF c1; 
        END LOOP; 
        ... 

Answer 12

To feng_zi(风子):
3. 我还是没法在CREATE TABLE或者INSERT INTO的子查询中用ORDER BY子句.TO gogoli(狗狗): UPDATABLE CURSOR的用法示例
    DECLARE 
        CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE; 
        ... 
    BEGIN 
        OPEN c1; 
        LOOP             FETCH c1 INTO ... 
            ... 
            UPDATE emp SET sal = new_sal WHERE CURRENT OF c1; 
        END LOOP; 
        ... 

Answer 13

to KingSunSha(弱水三千):
　你的数据库是多少呢,我用过8.0及以后的好象都没有问题

Answer 14

2搞错了，应改为：
create table temptable as select seq_id.nextval id,rid from (select rowid rid from tablename order by column);

Answer 15

最新结果: 在O8.16中测试带ORDER BY的子查询通过.
所以你的SQL可以写成:
update tablename x
   set id = (select rnum 
               from (select rownum rnum, tablename.* 
                       from tablename
                      order by b desc)
              where a= x.a);

Answer 16

谢谢大家,顺便问一下 KingSunSha,
你知道temporary table都什么时候用么?他存在的意义何在?

Answer 17

我猜你是说temporary tablespace吧？
每个用户都会设一个temporary tablespace，这个表空间是由oracle自己管理的。当用户在做大的查询（比如全表扫描）或者更新操作的时候，SGA中没有那么多空余的内存空间，ORACLE就会用到这个表空间。

Answer 18

不是temporary tablespace 是temporary table 

Answer 19

temporary table是在O8i才出来的概念吧？我没用过。
sql server中有这个概念，应该也是一个内存块吧，用户可以在sql块中生成、删除，一旦会话结束，临时表自动释放。

Answer 20

create global temporary table tablename……;
对，8.1之后才能临时表这个概念的，但这个临时表，据我了解当往表中插入记录时，不论是commit了，只要退出当前会话，表中的记录自动清空。
现在就是不太清楚，数据库重启之后，临时表是否依然存在
我今天创，明天来是如何的，你也可以试试，我感觉应该还存在.

Answer 21

to KingSunSha(弱水三千):
update tablename x
  set id = (select rnum 
              from (select rownum rnum, tablename.* 
                      from tablename
                      order by b desc)
              where a= x.a); 
执行的结果不是与update tablename set id=rownum;
一样吗？
在我这里
select rownum from tablename　order by b desc
与
select rownum from tablename;
得出的结果是不一样的，

Answer 22

TO feng_zi(风子):两句UPDATE的效果是不一样的.
如果用update tablename set id=rownum,得到的结果ID是按照物理存储顺序排列的(数据库自己管理,谁也不知道怎么排). 
如果用
 update tablename x
  set id = (select rnum 
              from (select rownum rnum, tablename.* 
                      from tablename
                      order by b desc)
              where a= x.a); 
那得到的结果就是这个问题想要的. 我想了一下,可以优化一下,如下:
 update tablename x
  set id = (select rnum 
              from (select rownum rnum, rowid 
                      from tablename
                      order by b desc)
              where rowid= x.rowid); 

Answer 23

to KingSunSha(弱水三千):
可是rownum好象已经与rowid所对应了，我是指在order by的情况下
SQL> select rownum,rowid from akai;   ROWNUM ROWID
--------- ------------------
        1 AAAGxXAANAAAE3OAAA
        2 AAAGxXAANAAAE3OAAC
        3 AAAGxXAANAAAE3OAAD
        4 AAAGxXAANAAAE3OAAFSQL> select rownum,rowid from akai order by akai;   ROWNUM ROWID
--------- ------------------
        1 AAAGxXAANAAAE3OAAA
        2 AAAGxXAANAAAE3OAAC
        4 AAAGxXAANAAAE3OAAF
        3 AAAGxXAANAAAE3OAAD
那么，在这样的情况下
update tablename x
  set id = (select rnum 
              from (select rownum rnum, rowid 
                      from tablename
                      order by b desc)
              where rowid= x.rowid); 
 
与
update tablename x
  set id = (select rnum 
              from (select rownum rnum, rowid 
                      from tablename)
              where rowid= x.rowid); 
没有什么区别呀，
在我这里，你的语句我试了，得出的结果与update tablename set id=rownum;一样，
是不是我数据库的原因呢？ 

Answer 24

现在所遇到的一些好象与以前接受的思维好象不一样似的，
好久没有对库进行操作了，写程序也只是写一些简单的sql而已，想捡回以前的东西，
拜托KingSunSha(弱水三千)，帮我解释一下。

Answer 25

To feng_zi(风子):
1、你漏看了desc了吧？
2、你可以做个实验，加入100百个纪录，然后随机删除一部分，在插入一些纪录，看看rowid和rownum还会不会对应？ ：） 就rowid来说，我用它完全是为了取代主键来定位纪录，从而提高性能。
3、你用的什么版本的oracle？我也是昨天才在 8.16上作了一些测试，上面给出的答案都是经过测试的

Answer 26

to KingSunSha(弱水三千):
我的意思并不是说rownum与rowid是对应的，
相对来说rownum只是一个行号,而rowid却是记录的物理位置，可以这么说（虽然8i以后不再使用扩展的rowid），
我的意思是对于整个表（也不存在where条件）时，rownum与rowid是对应着的，不管你是order by那个字段SQL> select rownum,rowid from akai order by akai desc;   ROWNUM ROWID
--------- ------------------
        3 AAAGxXAANAAAE3OAAD
        4 AAAGxXAANAAAE3OAAF
        2 AAAGxXAANAAAE3OAAC
        1 AAAGxXAANAAAE3OAAA我现在感觉越做越不对似的，这么简单的问题，好象思路总是绕不出，
我将我试验列出如下吧：
1、
SQL> desc akai
 列名                          可空值否   类型
 ------------------------------- -------- ----
 AKAI                                     VARCHAR2(1)
 XIAO                                     VARCHAR2(2)
 ID                                       NUMBER(5,2)SQL> select * from akai;
A XI        ID
- -- ---------
1 a
2 a
a
3 a2、要实现的如下：
SQL> select * from akai order by akai desc;
A XI        ID
- -- ---------
a            1
3 a          2
2 a          3
1 a          43、如果不用创临时表，我用的语句如下：
 update akai set id=(select rnum
                       from (select rownum rnum,rid
                               from (select rowid rid
                                       from akai
                                      order by akai desc)) a
        　　　　　　　 where a.rid=akai.rowid);
可以实现
4、如果使用
update akai x
  set id = (select rnum 
              from (select rownum rnum, rowid 
                      from akai
                      order by akai desc)
              where rowid= x.rowid); 
昨天用时没问题，今天却出现如下情况：
SQL> update akai x
  2    set id = (select rnum 
  3                from (select rownum rnum, rowid 
  4                        from akai
  5                        order by akai desc)
  6                where rowid= x.rowid); 
              where rowid= x.rowid)
                    *
第6行有错:
ORA-01446: 无法从含 DISTINCT、GROUP BY 等子句的视图中选择 ROWID
我将改之如下，应与原语句功能一样吧：
update akai x
  set id = (select rnum
              from (select rownum rnum, rowid rid
                      from akai
                      order by akai desc)
              where rid= x.rowid);实现的最后结果是：
SQL> select * from akai;
A XI        ID
- -- ---------
1 a          1
2 a          2
a            3
3 a          4
SQL> select * from akai order by akai desc;
A XI        ID
- -- ---------
a            3
3 a          4
2 a          2
1 a          1而使用update akai set id=rownum;
的结果同上;我的也是8.16