如何快速对数据进行有序化?

Question

在ORACLE 8i中，如何将一个表中的记录进行排序，并对相同数据项进行编号，且数据量较大。
如表        
      转换前                           转换后    
  SOUID     ITEM    SEQID      SOUID     ITEM       SEQID 
  12         AAA                12       AAA          1
  22         BBB                45       AAA          2
  32         CCC                70       AAA          3
  45         AAA                22       BBB          1
  52         BBB                52       BBB          2
  61         CCC                32       CCC          1
  70         AAA                61       CCC          2
请高手帮忙！

Answer 1

select souid,item,rank() over(partition by item order by souid) seqid
from tbname;

Answer 2

bzszp(SongZip) 的代码只有在9.0以上才可以的。　8.0没有分析函数

Answer 3

13:23:22 SQL> select col1,col2 from tb;COL1       COL2
---------- ------------------------------
1          aaa
2          aaa
3          aaa
4          bba
5          bbb
6          bbb
7          bbb
8          bbb
9          bbb
10         bbb已选择10行。已用时间:  00: 00: 00.47
13:23:28 SQL> select ttt.col1,ttt.col2,ttt.rn-tttt.mrn+1 r
13:23:32   2  from (select col1,col2,rownum rn from (
13:23:32   3  select * from tb order by col2) t) ttt,
13:23:32   4  (select col2,min(rn) mrn from (
13:23:32   5  select col1,col2,rownum rn from (
13:23:32   6  select * from tb order by col2) t
13:23:32   7                          ) tt group by col2) tttt
13:23:33   8  where ttt.col2=tttt.col2(+);COL1       COL2                                    R
---------- ------------------------------ ----------
1          aaa                                     1
2          aaa                                     2
3          aaa                                     3
4          bba                                     1
5          bbb                                     1
6          bbb                                     2
7          bbb                                     3
8          bbb                                     4
9          bbb                                     5
10         bbb                                     6已选择10行。已用时间:  00: 00: 00.47
13:23:33 SQL>

Answer 4

请问 bzszp(SongZip) 可否将结果存入一个临时表中？

Answer 5

可以。
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
   COL1  number,
   COL2  varchar2(20),
    col3  number
) ON COMMIT DELETE ROWS ;然后
insert into TABLENAME 
select ttt.col1,...

Answer 6

谢谢bzszp(SongZip) ！
完成了。