企业发票异常分析---分离进项与销项

今天做的是企业发票异常分析中：

zzsfp表可通过xf_id或者gf_id与nsrxx中的nsr_id进行关联，分离出销项发票表和进项发票表

最终成功分离出进项发票表和销项发票表：

步骤如下：

 

首先创建两个表：

 

create table xxfpb1(

 

hydm string,

 

xf_id string,

 

djzclx_dm string,

 

kydjrq string,

 

xgrq string,

 

label string,

 

fp_nid string,

 

je double,

 

se double,

 

jshj double,

 

kpyf string,

 

kprq string,

 

zfbz string

 

)row format delimited fields terminated by ',';

 

 

 

create table jxfpb1(

 

hydm string,

 

gf_id string,

 

djzclx_dm string,

 

kydjrq string,

 

xgrq string,

 

label string,

 

fp_nid string,

 

je double,

 

se double,

 

jshj double,

 

kpyf string,

 

kprq string,

 

zfbz string

 

)row format delimited fields terminated by ',';

 

分离语句：insert into jxfpb1 select t2.nsr_id,t3.gf_id,t2.djzclx_dm,

 

t2.kydjrq,t2.xgrq,t2.label,t3.fp_nid,t3.je,t3.se,t3.jshj,t3.kpyf,t3.kprq,t3.zfbz from nsrxx t2,zzsfp t3 where t3.gf_id=t2.hydm;

 

insert into xxfpb1 select t2.nsr_id,t3.gf_id,t2.djzclx_dm,

 

t2.kydjrq,t2.xgrq,t2.label,t3.fp_nid,t3.je,t3.se,t3.jshj,t3.kpyf,t3.kprq,t3.zfbz from nsrxx t2,zzsfp t3 where t3.xf_id=t2.hydm;

表数据：

 

 

 

 这样就成功将进项发票表和销项发票表进行了分离。