select * from
(select KIT,MMCU,IXTBM,IXKITL,IXLITM,IXUM,QNTY,F$RP,IXFORQ,IXITC,IXURCD,EFFF,EFFT from BillOfMaterialF3002
where IXTBM='M' and MMCU='B20100' and convert(varchar(10),EFFT,111) between '2009/01/01' and '2009/01/15'
)x full outer join
(select MCU,KIT,isnull(EFTJ,0)as EFTJ ,isnull(EXDJ,0)as EXDJ,isnull(UPRC,0)as UPRC from
(select KIT,MMCU from BillOfMaterialF3002 where IXTBM='M'
)a inner join ItemBasePriceFileF4106 on a.KIT=ItemBasePriceFileF4106.ITM and a.MMCU=MCU and convert(varchar(10),EXDJ,111) between '2009/01/01' and '2009/01/15' and MCU='B20100'
GROUP BY MCU,KIT,EFTJ,EXDJ,UPRC
)y on x.kit=y.kit and x.mmcu=y.mcu
執行后結果:
KIT, MMCU, IXTBM, ONTY..... MCU, KIT, UPRC
196339 B20100 M 10 NULL NULL NULL
NULL NULL NULL NULL B20100 196589 0.4660
NULL NULL NULL NULL B20100 198586 0.5113
150882 B20100 M 15 NULL NULL NULL
150882 B20100 M 20 NULL NULL NULL
使用全連接,所有的數據都會顯示。
在上面的裱中我想要KIT,MMCU,IXTBM,IXKITL字段在不能同時滿足的情況下,都顯示出來。
想要的結果如下:
KIT, MMCU, IXTBM, ONTY.....