1.
select a.bomcomponent,a.quantity,a.classtype,a.speprocuretypefbomitem from COC.MBOMITEM a where (materialbill, bomitemnodeno) in (select materialbill,bomitemnodeno
from COC.MBOMSITEMSELECT where materialbill='9166'
and (a.speprocuretypefbomitem <>'' or a.speprocuretypefbomitem is not null))
2.
select a.bomcomponent,a.quantity,a.classtype,a.speprocuretypefbomitem,bb.specprocuretype from COC.MBOMITEM a
join COC.PLANTMTL bb on a.bomcomponent=bb.mtlno
where (bb.specprocuretype <>'' or bb.specprocuretype is not null)
and (a.materialbill, a.bomitemnodeno) in (select materialbill,bomitemnodeno
from COC.MBOMSITEMSELECT where materialbill='9166')
最好可以给优化一下 我看着都晕了
------解决方案--------------------
- SQL code
1SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem FROM coc.mbomitem a WHERE (materialbill, bomitemnodeno) IN ( SELECT materialbill, bomitemnodeno FROM coc.mbomsitemselect WHERE materialbill = '9166' AND ( a.speprocuretypefbomitem <> '' OR a.speprocuretypefbomitem IS NOT NULL ))2SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem, bb.specprocuretype FROM coc.mbomitem a JOIN coc.plantmtl bb ON a.bomcomponent = bb.mtlno WHERE (bb.specprocuretype <> '' OR bb.specprocuretype IS NOT NULL) AND (a.materialbill, a.bomitemnodeno) IN ( SELECT materialbill, bomitemnodeno FROM coc.mbomsitemselect WHERE materialbill = '9166')
------解决方案--------------------
1用exist试试，不是绝对能快，但大部分时候能快
- SQL code
SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem FROM coc.mbomitem a WHERE exists ( SELECT 1 FROM coc.mbomsitemselect b WHERE materialbill = '9166' AND ( a.speprocuretypefbomitem <> '' OR a.speprocuretypefbomitem IS NOT NULL ) a.materialbill=b.materialbill and a.bomitemnodeno=a. bomitemnodeno)
------解决方案--------------------
2
- SQL code
SELECT a.bomcomponent, a.quantity, a.classtype, a.speprocuretypefbomitem, bb.specprocuretype FROM coc.mbomitem a, coc.plantmtl bb WHERE a.bomcomponent = bb.mtlno AND (bb.specprocuretype <> '' OR bb.specprocuretype IS NOT NULL) AND EXISTS ( SELECT 1 FROM coc.mbomsitemselect b WHERE materialbill = '9166' AND a.materialbill = b.materialbill AND a.bomitemnodeno = b.bomitemnodeno)
------解决方案--------------------
1 union 2 ,就可以了呀
------解决方案--------------------
想取并集的话还可以 用 Union All