oracle 在on的灵活使用

 

select * from    PR_T_Vhtb1010 A RIGHT JOIN  PR_T_VHTB1040 B
ON A.IDNO=B.IDNO and A.PLANTCODE=B.PLANTCODE and A.PSC=B.PSC   AND substr(A.SPEC ,58,1) =' ' where B.idno='X106110504'

查询有四条记录，虽然substr(A.SPEC ,58,1)=‘ ’不存在，但是ON A.IDNO=B.IDNO and A.PLANTCODE=B.PLANTCODE and A.PSC=B.PSC  依然可以查询出记录，因为RIGHT ON A表的数据不存在，B表数据仍然能查询到

 

select * from    PR_T_Vhtb1010 A RIGHT JOIN  PR_T_VHTB1040 B
ON A.IDNO=B.IDNO and A.PLANTCODE=B.PLANTCODE and A.PSC=B.PSC  where B.idno='X106110504'  AND substr(A.SPEC ,58,1) =' '

查询没有数据，对查询结果再进行过滤，数据不存在，等同于。

select * from    PR_T_Vhtb1010 A inner JOIN  PR_T_VHTB1040 B
ON A.IDNO=B.IDNO and A.PLANTCODE=B.PLANTCODE and A.PSC=B.PSC  AND substr(A.SPEC ,58,1) =' '  where B.idno='X106110504' 

一条记录在关联表匹配多行，返回最近的行

SELECT decode(S.PLANTCODE,null,1,S.PLANTCODE) AS PLANTCODE, T.PACKINGSPOT,T.CONTROLNO, T.CPDCOMPANY,T.PARTSNO,T.ORDERNO,T.SEQNO,T.INVOICENO, T.CASENO, T.PARTSNAMECHN,T.PARTSNAMEEN,T.SHIPPINGQTY AS SHIPPINGQTY,T.COSTWITHTAXES,T.PRICE, T.DADDTIME,T.DUPDTIME,T.CUPDUSER
FROM SP_M_SHP  T  LEFT JOIN (select row_number() over (partition by PARTSNO order by TIMEFROM DESC ) rn, partsno,cpdcompany,timefrom,timeto,plantcode from  sp_m_sitem ) S ON T.CPDCOMPANY=S.CPDCOMPANY AND T.PARTSNO=S.PARTSNO  AND S.rn=1   and S.TIMEFROM<=SUBSTR(T.CONTROLNO,3,8)  AND S.TIMETO>=SUBSTR(T.CONTROLNO,3,8)
WHERE SUBSTR(T.CONTROLNO,3,6) ='201412'  and T.CPDCOMPANY='GTCPD' AND T.PACKINGSPOT IN (select key1 from SP_M_CONS2  where id='PACKSPOT' and flg2='0') ;

或者

SELECT DECODE ((select first_value(plantcode) over (partition by PARTSNO order by TIMEFROM DESC ) plantcode from  sp_m_sitem S  WHERE S.PARTSNO=T.PARTSNO  and S.TIMEFROM<=SUBSTR(T.CONTROLNO,3,8)  AND S.TIMETO>=SUBSTR(T.CONTROLNO,3,8) ),NULL,1,(select first_value(plantcode) over (partition by PARTSNO order by TIMEFROM DESC ) plantcode from  sp_m_sitem S  WHERE S.PARTSNO=T.PARTSNO  and S.TIMEFROM<=SUBSTR(T.CONTROLNO,3,8)  AND S.TIMETO>=SUBSTR(T.CONTROLNO,3,8) )) AS PLANTCODE, T.PACKINGSPOT,T.CONTROLNO, T.CPDCOMPANY,T.PARTSNO,T.ORDERNO,T.SEQNO,T.INVOICENO, T.CASENO, T.PARTSNAMECHN,T.PARTSNAMEEN,T.SHIPPINGQTY AS SHIPPINGQTY,T.COSTWITHTAXES,T.PRICE, T.DADDTIME,T.DUPDTIME,T.CUPDUSER
FROM SP_M_SHP  T
WHERE SUBSTR(T.CONTROLNO,3,6) ='201412'  and T.CPDCOMPANY='GTCPD' AND T.PACKINGSPOT IN (select key1 from SP_M_CONS2  where id='PACKSPOT' and flg2='0')
;