客商申请单客商编码自动编码

create OR REPLACE VIEW  c4 as   ---建立试图C4
select 
  custcode,
  CONCAT(substr(0000000000,-(length(custcode)-length(custcode+1))),custcode+1) custcode2,
  id,
  CAST(id as int) ID2,      ---把编码右三位的列转换成数字      
  custname,
 areaclcode,
 pk_areacl              
  from 
   (
select 
  custcode,
  replace (substr(custcode,-4),‘*‘,‘0‘ ) id,   ---把编码右三位取出来
  custname,
  areaclcode,
  pk_areacl  
    from （       
select custcode ,
       custname ,
       areaclcode,
       bd_cubasdoc.pk_areacl   
     from bd_cubasdoc,bd_areacl
     where bd_cubasdoc.pk_areacl=bd_areacl.pk_areacl 
     ）aa
       where areaclcode like ‘07%‘         ---取地区分类07开头的行     
  )a
;
create OR REPLACE VIEW  c5 as   ---建立试图C5
select t1.maxid,t1.areaclcode,t2.custcode,t2.custcode2,t2.id,t2.custname,pk_areacl          --取C4表的值和分组统计的值
from 
(
select MAX(id2) maxid,areaclcode  from c4 group by areaclcode      ---根据地区分类分组，取ID2列（把编码右三位的列转换成数字）最大值
)t1
left join 
c4 t2
on t1.maxid=t2.id2 and t1.areaclcode=t2.areaclcode     ---表和视力的ID和地区分类名称都要相等

select * from C5