oracle 的sql处理方式

Question

现在有一个表
table1 
a1              a2      a3
04020020 压轮
A0101000 缝包线
A0101000 缝包线 白 18支/2X3
A0101001 缝包线 白 18支/2X3
A0101001 缝包线 白 20支/3X3
A0101001 缝包线 白 20支/8X1
A0101001 缝包线 红 20支/3X3如何用sql语句处理成如下表table2
b1(key_id)      b2              b3      b4
04020020001 04020020 压轮
A0101000001 A0101000 缝包线
A0101000002 A0101000 缝包线 白 18支/2X3
A0101001001 A0101001 缝包线 白 18支/2X3
A0101001002 A0101001 缝包线 白 20支/3X3
A0101001003 A0101001 缝包线 白 20支/8X1
A0101001004 A0101001 缝包线 红 20支/3X3

Answer 1

SQL> select * from table1;A1                   A2                   A3
-------------------- -------------------- --------------------
04020020             压轮                 
A0101000             缝包线               
A0101000             缝包线               白 18支/2X3
A0101001             缝包线               白 18支/2X3
A0101001             缝包线               白 20支/3X3
A0101001             缝包线               白 20支/8X1
A0101001             缝包线               红 20支/3X37 行 已选择SQL> 
SQL> with t as (select a.*,row_number() over(partition by a1 order by a2,a3 nulls first) rn from table1 a)
  2  select substr(a1,1,7) || to_char(rn) b1,a1 b2,a2 b3,a3 b4 from t
  3  /B1                                                     B2                   B3                   B4
------------------------------------------------------ -------------------- -------------------- --------------------
04020021                                               04020020             压轮                 
A0101001                                               A0101000             缝包线               
A0101002                                               A0101000             缝包线               白 18支/2X3
A0101001                                               A0101001             缝包线               白 18支/2X3
A0101002                                               A0101001             缝包线               白 20支/3X3
A0101003                                               A0101001             缝包线               白 20支/8X1
A0101004                                               A0101001             缝包线               红 20支/3X37 行 已选择

Answer 2

LS上的差不多了，只是B1的格式在改下就可以了。