求SQL优化，ASE
selectJRNJRNNO, JRNTR_COD, JRNAC_DAT, nkgl.fun_formattimestr(JRNTR_TIM) JRNTR_TIM,@var_year+JRNTR_DAT JRNTR_DAT,JRNTL_ID,JRNSUP1ID,JRNSUP2ID,JRNCB,JRNBR, JRNAUTLVL, JRNVCHCNT,JRNVCHNO,JRNVCHEC,JRNINPARA1,@trandate BAKDATE,JRNTR_TIM JRNTR_TIM2,JRNTM_NO,
case when b.SCVJRNNO is null
then '1' else '0'
end as CASHFLAG
from (select JRNJRNNO,JRNTR_COD,JRNAC_DAT,JRNTR_TIM,JRNTR_DAT,JRNTL_ID,JRNSUP1ID,JRNSUP2ID,JRNCB,JRNBR, JRNAUTLVL,
JRNVCHCNT,JRNVCHNO,JRNVCHEC,JRNINPARA1,JRNTM_NO
from BCP_JRN_B
where JRNTR_FRM='TERM')a
left join (select SCVJRNNO
from BCP_SCV_B b
where b.SCVVHAC0+ b.SCVVHAC1+b.SCVVHAC2+b.SCVVHAC3+b.SCVVHAC4+b.SCVVHAC5+b.SCVVHAC6+b.SCVVHAC7like '%'+SCVTRBR+'01940000001%')b
on a.JRNJRNNO = b.SCVJRNNO
先通过BCP,把BCP_SCV_B,BCP_JRN_B导入数据
BCP_SCV_B有 190万的数据量，BCP_JRN_B日志表有340万，
BCP_SCV_B表中从SCVVHAC0到SCVVHAC7有一个符合条件，即可。
BCP_SCV_B是从表,BCP_SCV_B日志表为主，通过SCVJRNNO，JRNJRNNO 字段关联，
页:
[1]