优化后的sql 语句 oracle

用or  用union 代替   union 是去重 没有union all 效率高

case when then 用DECODE(mbi.isvalidatebill,NULL,0,0,0,1) IsValidateBill, 代替

 

with rs as
 (select formcode, bill.customerorder, bill.delivercode
    from fhl_SCS.Sc_Bill bill
   where BILL.IsDeleted = 0
     and bill.formcode = upper('11602293620274')
  UNION ALL
  select formcode, bill.customerorder, bill.delivercode
    from fhl_SCS.Sc_Bill bill
   where BILL.IsDeleted = 0
     and bill.customerorder = upper('11602293620274')
   UNION ALL
  select formcode, bill.customerorder, bill.delivercode
    from fhl_SCS.Sc_Bill bill
   where BILL.IsDeleted = 0
     and bill.delivercode = upper('11602293620274'))
SELECT BILL.FormCode,
       BILL.CustomerOrder,
       BILL.DeliverCode,
       BILL.DeliverStationID StationId,
       ecp.CompanyName StationName,
       ecp.SiteNo,
       ecp.simplespell StationPinYin,
       ecp.CompanyFlag,
       0 NeedAmount,
       BInfo.PackageCount,
       BILL.Status,
       BILL.ReturnStatus,
       ob.departureid LastOutBoundDepartment,
       0 IsInbounding,
       DECODE(mbi.isvalidatebill,NULL,0,0,0,1) IsValidateBill,
       DECODE(BInfo.IsValidateBill,1,1,0) IsHasValidateBill,
       DECODE(BILL.InBoundKey,NULL,1,0) IsFirstInbound,
       BILL.MerchantId,
       mbi.isformcode,
       mbi.iswaybillno,
       mbi.isdelivercode
  FROM fhl_SCS.SC_Bill BILL
  JOIN rs
    on bill.formcode = rs.formcode
  JOIN fhl_SCS.SC_BillInfo BInfo
    ON fhl_SCS.BILL.formcode = BInfo.formcode
  JOIN MerchantBaseInfo mbi
    ON BILL.Merchantid = mbi.ID
  JOIN ExpressCompany ecp
    on bill.deliverstationid = ecp.expresscompanyid
  left Join fhl_SCS.SC_Inbound SIB
    ON BILL.InBoundKey = SIB.IBID
  left Join fhl_SCS.Sc_Outbound ob
    on Bill.Outboundkey = ob.obid
 inner join fhl_dcs.ordercomplexquery ocq
    ON to_char(ocq.waybillno) = rs.formcode
   and ocq.formcode = rs.customerorder
   and ocq.delivercode = rs.delivercode
   and OCQ.ISDELETED = 0
 WHERE bill.returnstatus is null
   and not OCQ.Querystatus in (1, 2, 3, 4, 5, -9);