Oracle多条件判断比对

--VIN错误
select VIN 
      from DB_INFOMATION i
      where length(i.VIN)!=17
      ;
      
--VIN重复
select VIN 
      from DB_INFOMATION i
      group by VIN
      having count(vin) > 1
      ;
      
--不存在的
select DISTINCT VIN 
      from DB_INFOMATION i
      where i.CLXH not in 
      (
      select n.CLXH
          from DB_NOTICEPARAM n
      )
      ;
      
--完全匹配
select DISTINCT VIN 
      from DB_INFOMATION i, DB_NOTICEPARAM n
      where i.CLXH=n.CLXH
        and DECODE(i.DCDTXX_XH,null,'N/A',i.DCDTXX_XH)=DECODE(n.DTXH,'NA','N/A',n.DTXH)
        and trim(TO_SINGLE_BYTE(DECODE(i.DCDTXX_SCQY,null,'N/A',i.DCDTXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.DTSCQY,'NA','N/A',n.DTSCQY)))
        and DECODE(i.DCZXX_XH,null,'N/A',i.DCZXX_XH)=DECODE(n.CXXH,'NA','N/A',n.CXXH)
        and DECODE(i.DCZXX_ZRL,null,'N/A',i.DCZXX_ZRL)=DECODE(n.DCZZRL,'NA','N/A',n.DCZZRL)
        and trim(TO_SINGLE_BYTE(DECODE(i.DCZXX_SCQY,null,'N/A',i.DCZXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.DCZSCQY,'NA','N/A',n.DCZSCQY)))
        and DECODE(i.QDDJXX_XH_1,null,'N/A',i.QDDJXX_XH_1)=DECODE(n.QDDJXH,'NA','N/A',n.QDDJXH)
        and DECODE(i.QDDJXX_EDGL_1,null,'N/A',i.QDDJXX_EDGL_1)=DECODE(n.QDDJEDGL,'NA','N/A',n.QDDJEDGL)
        and trim(TO_SINGLE_BYTE(DECODE(i.QDDJXX_SCQY_1,null,'N/A',i.QDDJXX_SCQY_1)))=trim(TO_SINGLE_BYTE(DECODE(n.QDDJSCQY,'NA','N/A',n.QDDJSCQY)))
        and DECODE(i.RLDCXX_XH,null,'N/A',i.RLDCXX_XH)=DECODE(n.RLDCXH,'NA','N/A',n.RLDCXH)
        and DECODE(i.RLDCXX_EDGL,null,'N/A',i.RLDCXX_EDGL)=DECODE(n.RLDCEDGL,'NA','N/A',n.RLDCEDGL)
        and trim(TO_SINGLE_BYTE(DECODE(i.RLDCXX_SCQY,null,'N/A',i.RLDCXX_SCQY)))=trim(TO_SINGLE_BYTE(DECODE(n.RLDCSCQY,'NA','N/A',n.RLDCSCQY)))
        ;
        
--取不匹配最高批
select i.VIN,
      NULLIF(DECODE(n.DTXH,'NA','N/A',n.DTXH),DECODE(i.DCDTXX_XH,null,'N/A',i.DCDTXX_XH)) AS DCDTXX_XH,
      NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.DTSCQY,'NA','N/A',n.DTSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.DCDTXX_SCQY,null,'N/A',i.DCDTXX_SCQY)))) AS DCDTXX_SCQY,
      NULLIF(DECODE(n.CXXH,'NA','N/A',n.CXXH),DECODE(i.DCZXX_XH,null,'N/A',i.DCZXX_XH)) AS DCZXX_XH,
      NULLIF(DECODE(n.DCZZRL,'NA','N/A',n.DCZZRL),DECODE(i.DCZXX_ZRL,null,'N/A',i.DCZXX_ZRL)) AS DCZXX_ZRL,
      NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.DCZSCQY,'NA','N/A',n.DCZSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.DCZXX_SCQY,null,'N/A',i.DCZXX_SCQY)))) AS DCZXX_SCQY,
      NULLIF(DECODE(n.QDDJXH,'NA','N/A',n.QDDJXH),DECODE(i.QDDJXX_XH_1,null,'N/A',i.QDDJXX_XH_1)) AS QDDJXX_XH_1,
      NULLIF(DECODE(n.QDDJEDGL,'NA','N/A',n.QDDJEDGL),DECODE(i.QDDJXX_EDGL_1,null,'N/A',i.QDDJXX_EDGL_1)) AS QDDJXX_EDGL_1,
      NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.QDDJSCQY,'NA','N/A',n.QDDJSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.QDDJXX_SCQY_1,null,'N/A',i.QDDJXX_SCQY_1)))) AS QDDJXX_SCQY_1,
      NULLIF(DECODE(n.RLDCXH,'NA','N/A',n.RLDCXH),DECODE(i.RLDCXX_XH,null,'N/A',i.RLDCXX_XH)) AS RLDCXX_XH,
      NULLIF(DECODE(n.RLDCEDGL,'NA','N/A',n.RLDCEDGL),DECODE(i.RLDCXX_EDGL,null,'N/A',i.RLDCXX_EDGL)) AS RLDCXX_EDGL,
      NULLIF(trim(TO_SINGLE_BYTE(DECODE(n.RLDCSCQY,'NA','N/A',n.RLDCSCQY))),trim(TO_SINGLE_BYTE(DECODE(i.RLDCXX_SCQY,null,'N/A',i.RLDCXX_SCQY)))) AS RLDCXX_SCQY
      from DB_INFOMATION i, (select distinct * from DB_NOTICEPARAM where (clxh,ggpc) in (select clxh,max(GGPC) AS GGPC from DB_NOTICEPARAM group by clxh)) n
      where i.CLXH=n.CLXH
      ;