生成自然顺序，和自定义顺序的编号

生成自然顺序，和自定义顺序的编号

 

 

select 'E' || trim(to_char(nvl(a, 0), '00000000')) customer_id--固定位数填充

  from (select t.a, t1.a b

          from (select rownum a

                  from dual

                connect by rownum <=

                           (select max(to_number(replace(customer_id, 'E', ''))) + 2

                              from tb_cus_firm

                             where customer_id like 'E%')) t,

               (select to_number(replace(customer_id, 'E', '')) a

                  from tb_cus_firm

                 where customer_id like 'E%') t1

         where t.a not like '%4%' and t.a = t1.a(+))

 where b is null and rownum = 1

 

 解析：

 

 select a,rownum

  from (select t.a, t1.a b

          from (select rownum a

                  from dual

                connect by rownum <=

                           (select max(to_number(replace(customer_id, 'E', ''))) + 2

                              from tb_cus_firm

                             where customer_id like 'E%')) t,

               (select to_number(replace(customer_id, 'E', '')) a

                  from tb_cus_firm

                 where customer_id like 'E%') t1

         where t.a not like '%4%' and t.a = t1.a(+))

 /*where b is null*/ /*and rownum = 1*/ rownum是根据当前的结果集在作用域内变化，这句就保证了跳过之后还从未用的顺序开始

 

同样的两列比较：取理论上对比实际为null的那些的第一个