日期:2014-05-17  浏览次数:20795 次

这里有两条sql语句 我想取他们的并集
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


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')

------解决方案--------------------

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