求一sql语句

Question

表A有两列,一列为机器名machinename,一列为机器名序号machineno,结构如下
                  machineno    machinename  
                              A_35Bz
                              A_37Nu
                              A_...   
                              B_56Hk
                              B_26Fe
                              B_...   现在的machinenl列数据为空,现在我想统计以A_,B_开头的machinename总数并循环A_,B_总数连续插入machineno列,以A_,B_开头,目标数据结构如下,
                machineno    machinename  
                 A_1           A_35Bz
                 A_2           A_37Nu
                 A_N..         A_...   
                 B_1           B_56Hk
                 B_2           B_26Fe
                 B_N..         B_...  不知oracle上此sql怎么写?

Answer 1

update a t1
   set machineno = (select machineno
                      from (select machinename,
                                   substr(machinename, 1, 2) || rank() over(partition by substr(machinename, 1, 2) order by machinename) machineno
                              from a) t2
                     where t1.machinename = t2.machinename)

Answer 2

把rank()改成row_number()更好
因为rank()出来的数字有可能相同

Answer 3

如果machinename有相同的情况
那就需要用到rowid才行

Answer 4

SQL> select * from t;MACHINENO  MACHINENAME
---------- ------------------------------
           A_35Bz
           A_37Nu
           A_a
           A_b
           B_56Hk
           B_26Fe
           B_267 rows selectedSQL> 
SQL> update t
  2  set t.machineno=( select rn from
  3                              (
  4                                select rowid idx,machineno,machinename,
  5                                substr(machinename,1,2)||row_number()over(partition by substr(machinename,1,2)
  6                                order by substr(machinename,1,2))rn
  7                                from t
  8                              ) B where B.idx = t.rowid
  9                    );7 rows updatedSQL> select * from t;MACHINENO  MACHINENAME
---------- ------------------------------
A_1        A_35Bz
A_2        A_37Nu
A_3        A_a
A_4        A_b
B_1        B_56Hk
B_2        B_26Fe
B_3        B_267 rows selected