一些常用的sql或者XML语句

1.批量插入的方法

  <!-- 保存订单完成率指标数据 -->
    <insert id="insertSmtOrThtOrderCompletionRateList" parameterType="beat.model.OrderCompletionRateDto">
        insert into rt_order_completion_rate_dz(
        base_code, factory_code, floor_code,
        workline, cumulative_date,
        order_no, material_no,
        plan_prod_count,
        report_count, is_completed,shift, crdate,crname)
        <foreach collection ="rateList" item="item" index= "index" separator ="union all">
            select
            #{item.baseCode, jdbcType = VARCHAR},
            #{item.factoryCode, jdbcType = VARCHAR},
            #{item.floorCode, jdbcType = VARCHAR},
            #{item.workLine, jdbcType = VARCHAR},
            to_date(#{item.cumulativeDate, jdbcType = VARCHAR},'yyyy-MM-dd'),
            #{item.orderNo, jdbcType = VARCHAR},
            #{item.materialNo, jdbcType = VARCHAR},
            #{item.planProdCount, jdbcType = INTEGER},
            #{item.reportCount, jdbcType = INTEGER},
            #{item.isCompleted, jdbcType = INTEGER},
            #{item.shift, jdbcType = INTEGER},
            sysdate,'sa'
            from dual
        </foreach>
    </insert>

2.添加字段和添加注释

alter table rt_order_completion_rate_dz add(shift number(1));
COMMENT ON COLUMN rt_order_completion_rate_dz.shift IS '白晚班标识0白班1晚班';

3. anti反连接很牛逼的样子先记下来

select * from orders left anti join order_items on  ((orders.order_id = order_items.order_id) or isnull((orders.order_id = order_items.order_id)))

4.Oracle虚拟表

  select * from (
            select v.werks,v.lgort,v.mblnr,decode(v.rsnum,'0000000000','',v.rsnum) as rsnum,v.xblnr,v.matnr,v.menge,
            ls.storage,nvl(od.output_qty,0) as outputQty,round(nvl(v.menge,0)-nvl(od.output_qty,0)) diffQty,
            row_number() over(partition by v.werks,v.lgort,v.mblnr,v.matnr order by v.matnr) rn
            from (
            <foreach collection="list" item="i" separator="union all">
                select #{i.werks,jdbcType=VARCHAR} as werks,
                    #{i.lgort,jdbcType=VARCHAR} as lgort,
                    #{i.mblnr,jdbcType=VARCHAR} as mblnr,
                    #{i.rsnum,jdbcType=VARCHAR} as rsnum,
                    #{i.xblnr,jdbcType=VARCHAR} as xblnr,
                    substr(#{i.matnr,jdbcType=VARCHAR},length(#{i.matnr,jdbcType=VARCHAR})-8) as matnr,
                    #{i.menge,jdbcType=INTEGER} as menge
                from dual
            </foreach>
                ) v
                inner join ma_lgort_relation lc on v.werks=lc.WERKS_SAP and v.lgort=lc.LGORT_SAP
                inner join factory_relation fr on v.werks=fr.werks_sap
                left join ma_line_store_storage ls
                on ls.lgort=concat('M',lc.LGORT) and ls.WERKS=fr.werks and ls.matnr=v.matnr
                left join ma_output_order_detail od
                on od.lgort=concat('M',lc.LGORT) and od.WERKS=fr.werks and od.mblnr=v.mblnr and od.matnr=v.matnr
                where (ls.werks is null or ls.werks=#{werks,jdbcType=VARCHAR})
                and (od.werks is null or od.werks=#{werks,jdbcType=VARCHAR})
                and concat('M',lc.LGORT)=#{lgort,jdbcType=VARCHAR}
        ) where rn=1

5.电装尾单复杂sql

 with v1 AS (
   select wknum,ktsch,crdate,(sysdate - crdate) as overdueTime from ( select wknum,ktsch,crdate,rankNo from(  select wknum ,ktsch ,crdate ,rank() over(partition by a.wknum order by a.ktsch desc ,a.crdate desc) rankNo
          from    meshik.SMT_WO_REPORT a   WHERE a.KTSCH IN('DZ01','DZ02') ) e where e.rankNo=1) c where c.wknum='1412130001' 
   )

    ,
   v2 as (
     select v1.wknum,
     count(case when s.process='631'  then 1 else null end ) as plugWaitNum,
     count(case when s.process='641'  then 1 else null end ) as testWaitNum
      from v1 left join  meshik.WORKORDER_BARCODE_STATUS s  on  v1.wknum= s.wknum  group by v1.wknum
   )
   ,
   v3 as (
     select wknum,SMT_CRDATE,rankNo from(  select a.wknum  ,b.SMT_CRDATE ,rank() over(partition by a.wknum order by b.SMT_CRDATE asc) rankNo
            from  v1 a left join  meshik.PE_DENSO_STOCK_RECORD  b  on  a.wknum= b.wknum ) e where e.rankNo=1
          
   )
   ,
    v4 as (
   select a.wknum,
      nvl(sum(case when b.SMT_CRDATE is not null then (b.SMT_CRDATE - a.crdate) else 0 end ),0) as fistWarehousingCycle from  v1 a left join v3 b  on  a.wknum= b.wknum   group by a.wknum
   ) 
   ,
    v5 as (
     select wknum,SMT_CRDATE,rankNo from(  select a.wknum  ,b.SMT_CRDATE ,rank() over(partition by a.wknum order by b.SMT_CRDATE desc) rankNo
            from  v1 a left join  meshik.PE_DENSO_STOCK_RECORD  b  on  a.wknum= b.wknum ) e where e.rankNo=1
          
   ),
    v6 as (
   select a.wknum,
      nvl(sum(case when b.SMT_CRDATE is not null then (b.SMT_CRDATE - a.crdate) else 0 end ),0) as lastWarehousingCycle from  v1 a left join v5 b  on  a.wknum= b.wknum   group by a.wknum
   )  
   ,
   v7 as (
   select v1.wknum,
     nvl(sum(b.IN_NUM),0) as InNum from  v1 left join  meshik.PE_DENSO_STOCK_RECORD  b  on  v1.wknum= b.wknum  group by v1.wknum
 )
   ,
   v8 as (
     select distinct substr(wo.wknum,0,10) as wknum,wo.wemng,po.aufnr,po.matnr,po.maktx,po.gltrs,po.psmng,pg.grwemng ,decode(nvl(pg.grwemng,0),0,0,round(pg.grwemng/po.psmng,4))  as ratio,
             case when  ( to_number(trunc(sysdate,'dd') - trunc(po.gltrs,'dd')) between 0 and 4
                                  and decode(nvl(pg.grwemng,0),0,0,round(pg.grwemng/po.psmng,4))<1
                                  and decode(nvl(pg.grwemng,0),0,0,round(pg.grwemng/po.psmng,4))>=0.9
                                or
                                  to_number(trunc(sysdate,'dd') - trunc(po.gltrs,'dd')) > 4
                                  and decode(nvl(pg.grwemng,0),0,0,round(pg.grwemng/po.psmng,4)) != 1
                                )
                                 then 1 else 0 end  as weidan
            from v1 left join
            meshik.workorder wo on  substr(wo.wknum,0,10)=v1.wknum
            left join meshik.production_order po
            on wo.aufnr = po.aufnr
            left join meshik.workline wl
            on wo.mainpline = wl.pline
            left join meshik.floor f
            on wl.floor_code = f.floor_code
            left join meshik.PRODUCTION_ORDER_GR pg
            on po.aufnr = pg.aufnr
            where
              wo.wosta in ('INPR','RECO')
                and wo.wktype in ('DZ01', 'DZ02', 'THT')
                and po.psmng != nvl(pg.grwemng,0)          
   )
   
      select
      v8.aufnr,wo.mainpline as pline, v1.crdate as reportTime,v2.plugWaitNum,v2.testWaitNum,v3.SMT_CRDATE as warehousingTime,v4.fistWarehousingCycle,v7.InNum,v6.lastWarehousingCycle,v8.ratio,v8.weidan as tailOrder,v1.overdueTime
      from v1 
      left join v2 on v1.wknum=v2.wknum
      left join v3 on v1.wknum=v3.wknum
      left join v4 on v1.wknum=v4.wknum
      left join v6 on v1.wknum=v6.wknum
      left join v7 on v1.wknum=v7.wknum
      left join v8 on v1.wknum=v8.wknum
      left join meshik.workorder wo on  substr(wo.wknum,0,10)=v1.wknum 









----------------------------------------------------------
aufnr   订单号
pline    产线
reportTime   SMT最后一批报工时间
plugWaitNum  插件停留数量
testWaitNum  测试停留数量
warehousingTime   首批入库时间
fistWarehousingCycle   首批入库周期
InNum       订单入库数量
lastWarehousingCycle    订单累计入库周期
ratio     入库比例
tailOrder   是否尾单 （1是0否）
overdueTime  入库超期时数 （天）