WITH S1 as (
SELECT HANDLE ,SITE,OPERATION,DCOP_NUMBER,RESRCE
,CREATED_DATE_TIME
,TO_CHAR(CREATED_DATE_TIME +INTERVAL '8' HOUR,'YYYY-MM-DD HH24:MI:SS') as DTime
,TYRE_CODE
FROM Z_DCOP_COLLECT_LOG
)
select distinct S1.* ,S2.PARAMETER_NO,S5.DC_VALUE_MASK ,S2.VALUE
,S3.START_USER ,S4.BIG_PRINT,S4.PRODUCT_DESCRIPTION,S5.FS
,S5.DESCRIPTION
from S1
inner join Z_DCOP_COLLECT_DETAIL_LOG S2 on S1.HANDLE=S2.DCOP_COLLECT_BO and S1.SITE=S2.SITE
inner join
(
select distinct TYRE_CODE,SITE,START_USER,ITEM_BO
from Z_CUSTOM_PRODUCTION_LOG
) S3 on S1.TYRE_CODE=S3.TYRE_CODE and S1.SITE=S3.SITE
inner join (
select distinct ITEM,SITE,BIG_PRINT,PRODUCT_DESCRIPTION
from Z_CUSTOM_ITEM
) S4 on S4.item=SUBSTR(S1.DCOP_NUMBER,1,INSTR(S1.DCOP_NUMBER,'_',1,1)-1) and S4.SITE=S1.SITE
inner join (
select DC_GROUP_BO,PARAMETER_NAME
,DC_VALUE_MASK,DESCRIPTION
,(case when substr(PARAMETER_NAME,1,instr("PARAMETER_NAME",'_',1,1)-1)='UF' then 'UF'
when substr(PARAMETER_NAME,1,instr("PARAMETER_NAME",'_',1,1)-1)='DB' then 'DB'
when substr(PARAMETER_NAME,1,instr("PARAMETER_NAME",'_',1,1)-1)='WEIGHT' then '磅秤'
else '其他' end ) as FS
from DC_PARAMETER where (DC_VALUE_MASK like '1201%' or DC_VALUE_MASK like '1202%') and length(DC_VALUE_MASK)=7
) S5 on S2.PARAMETER_NO=S5.PARAMETER_NAME and S1.SITE=SUBSTR(S5.DC_GROUP_BO,11,4)
where 1=1 ${if(len(生产厂区)=0,""," and S1.site='"+生产厂区+"'")}
${if(len(测定机台)=0,""," and S1.RESRCE='"+测定机台+"'")}
${if(len(FS)=0,""," and FS='"+FS+"'")}
${if(len(轮胎规格)=0,""," and PRODUCT_DESCRIPTION='"+轮胎规格+"'")}
${if(len(起始条码)=0,""," and TO_NUMBER(S1.TYRE_CODE)>='"+起始条码+"'")}
${if(len(结束条码)=0,""," and TO_NUMBER(S1.TYRE_CODE)<='"+结束条码+"'")}
and DTime
between '${开始时间}'||' '||'${开始小时}'||':'||'${开始分钟}'
and '${结束时间}'||' '||'${结束小时}'||':'||'${结束分钟}'如图：这一段sql执行时间2s左右 从where 后面开始这些参数都是丢到帆软里面的，我在帆软加上参数 ，结果出不来，说sql执行时间过长，先不说去决策系统改时长，我想知道为啥？因为我参数过多吗？在帆软的数据集里面注释这些参数后，嘿，结果几秒能出来200行，加了参数就一直出不来，按理说总数据就那么多，咋会执行效率这么低？我这sql是不是要优化的，各位路过的大神有没有啥好办法？就是一直这种状态，而且我感觉这时间也不是很长
