SQL优化方案

1:建立中间表，将步骤分解。

2:避免全字段查询，只查需要的字段

3:限定条件查询，避免先关联后写条件，

 

--优化交货数据  --建临时表T_JHinfo  select A.VBELN,C.VBELN AS JH_VBELN,C.ERDAT AS JH_ERDAT,C.WADAT_IST   from (          select VBELN,case when AUART='ZA17' then BSTNK else  VBELN end as VBELNNO          from VBAK          where ERDAT=CONVERT(varchar(10),GETDATE()-1,112)          )A left join (select VBELN,VGBEL from LIPS) AS B ON A.VBELNNO=B.VGBEL             LEFT jOIN (select VBELN,ERDAT,WADAT_IST FROM LIKP) AS C ON B.VBELN=C.VBELN

select A.ERDAT AS 对接日期, A.ERZET AS 对接时间,A.ORDERID AS 订单号,DDLX AS 订单类型,A.KUNNR AS 客户编号, B.POSNR1 AS 订单行项目,B.MATNR AS 物料编号,KWMENGE AS 数量,SLPRC AS 单价,BUKRS AS 销售组织,B.VKBUR AS 销售办公室, B.VBELN as SAP订单号,POSNR AS SAP订单行项目,ZMENG AS SAP订单受理数量,LIFSK AS SAP订单交货冻结, C.ERDAT AS SAP订单创建日期, F.JH_VBELN AS SAP交货单号,F.JH_ERDAT AS SAP交货单创建日期,F.WADAT_IST AS SAP交货单过帐日期, B.REASON as 拒绝原因,case when  A.DDLX='08' AND B.VBELN='' AND B.REASON='' then '待审核'         when A.DDLX='08' AND LIFSK='ZE'  then '已审核，待付款'         when  A.DDLX='08' AND LIFSK='ZF'  then '已付款，待发货'         when  A.DDLX='08' AND F.WADAT_IST<>'' then '已发货'         when  A.DDLX='08' AND  B.REASON<>'' then '已拒绝'         when  A.DDLX<>'08' AND B.VBELN='' AND B.REASON='' then '待受理'         when  A.DDLX<>'08' AND B.VBELN<>'' AND F.WADAT_IST='' then '已受理待发货'         when  A.DDLX<>'08' AND F.WADAT_IST<>'' then '已发货'         end as 订单状态 from (select ERDAT,ERZET,ORDERID,DDLX,KUNNR,BUKRS   from ZTSD200 where ERDAT=CONVERT(Varchar(10),GETDATE()-1,112) ) AS  A left join (select ORDERID,POSNR1,MATNR,KWMENGE,SLPRC,VKBUR,VBELN,REASON                       from  ZTSD200_item                       where ORDERID IN (select ORDERID from ZTSD200 where ERDAT=CONVERT(Varchar(10),GETDATE()-1,112) )        ) AS  B ON A.ORDERID=B.ORDERID                left join (select VBELN,LIFSK,ERDAT from VBAK where ERDAT=CONVERT(Varchar(10),GETDATE()-1,112)) C ON B.VBELN=C.VBELN                LEFT JOIN (select VBELN,POSNR,ZMENG from VBAP where ABGRU not in ('X2','X6') AND ERDAT=CONVERT(Varchar(10),GETDATE()-1,112)) D ON B.VBELN=D.VBELN       LEFT jOIN T_JHinfo AS F ON C.VBELN=F.VBELN