左鏈接后出現多余的錯誤數據？？？？？？？

[susie8449]

SELECT distinct rr.createdate,
rr.productno,
round((aa.defaultvalue * bb.defaultvalue / 1000000 /
mm.pcs_spnl),
5) as area,
pp.unitebase,
cc.base,
nn.cuthick,
dd.subwidth,
ee.sublng,
ff.qty,
oo.drillqty,
qq.under1,
qq.between1,
qq.above1,
(qq.under1 + qq.above1 + qq.between1) as total,
gg.ppspec,
hh.cuspec,
ii.words,
jj.Athick,
kk.Agf,
ll.plating
from (select productno, createdate from tblengproductdata) rr
left join (SELECT distinct A.PRODUCTNO as ERP料號, A.DEFAULTVALUE
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'spnl size(x)') AA on substr(aa.erp料號,
1,
length(aa.erp料號) - 2) =
rr.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'spnl size(y)') BB on aa.erp料號 =
bb.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as pcs_spnl
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'pcs/spnl') mm on aa.erp料號 = mm.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as unitebase
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'PCS/CCL') pp on aa.erp料號 = pp.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as base
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'CCL thickness') cc on aa.erp料號 =
cc.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as cuthick
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'inner copper') nn on aa.erp料號 =
nn.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as subwidth
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'SubWidth') dd on aa.erp料號 = dd.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as sublng
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'SubLng') ee on aa.erp料號 = ee.productno
left join (SELECT A.PRODUCTNO, A.defaultvalue as qty
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'Pcs/WPNL') ff on aa.erp料號 = ff.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as drillqty
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'drl. lam. QTY') oo on aa.erp料號 =
oo.productno
left join (SELECT itemno,
sum(case
when holesize < 0.35 then
holeqty
Else 0
End) under1,
sum(case
when holesize > 0.35 and holesize < 0.5 then
holeqty
Else
0
End) between1,
sum(case
when holesize > 0.5 then
holeqty
Else
0
end) above1
FROM tblEngProdDrillStructure
group by itemno) qq on aa.erp料號 = qq.itemno
left join (SELECT itemno,
SUBSTR(MAX(SYS_CONNECT_BY_PATH(citemno, ';')), 2) ppspec
FROM (SELECT itemno,
citemno,
rn,
LEAD(rn) OVER(PARTITION BY itemno ORDER BY rn) rn1
FROM (SELECT itemno,
citemno,
ROW_NUMBER() OVER(ORDER BY citemno) rn
FROM tblengprodlayerpress
where lamtype = '膠片'))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY itemno) gg on aa.erp料號 = gg.itemno
left join (SELECT T.itemno, t.lamtype, t.citemno as cuspec
FROM tblengprodlayerpress T
where lamtype = '銅箔') hh on aa.erp料號 = hh.itemno
left join (SELECT A.productno,
(case
when a.defaultvalue <> '' or a.defaultvalue <> '-' then
'Y'
Else
'N'
end) words
FROM Tblengproductattribdetail A
where A.attribno = 'S/M sides') ii on aa.erp料號 = ii.productno
left join (SELECT A.PRODUCTNO, A .DEFAULTVALUE as Athick
FROM tblEngProductAttribDetail A
where A.attribno = 'gold thickness(ENIG)') jj on aa.erp料號 =
jj.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as Agf
FROM tblEngProductAttribDetail A
where A.attribno = 'G/F QTY per pcs') kk on aa.erp料號 =
kk.productno
left join (SELECT A.PRODUCTNO, A.DEFAULTVALUE as plating
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'plating gold thickness') ll on aa.erp料號 =
ll.productno
where length(rr.productno) = 13
And (rr.createdate >= To_Date('2008-01-01 ', 'YYYY/MM/DD') And
rr.createdate <= To_Date('2008-06-06 ', 'YYYY/MM/DD '))


我對其中的子查詢都能夠顯示正確的結果，但是用左鏈接后，會出現多余的錯誤數據。為什么？
希望能夠有人為我指點。謝謝！

[susie8449]

我剛剛把left join 全部改為left outer join后，還是有多余的錯誤數據顯示：
2006-05-14 184H08027D245 0.0718 36.5"/6*2*1*1=12PCS 36.5 48.5
2006-05-14 184H08027D245 0.0718 43"/4*2*2*1=16PCS 43 49
正確數據應該是上面一行，不知道下面的錯誤數據從哪里來的？郁悶...

[susie8449]

select rr.productno,rr.createdate,aa.基板排版數量 from
(select productno, createdate
from tblengproductdata )rr left outer join

(SELECT A.PRODUCTNO, A.DEFAULTVALUE as 基板排版數量
FROM TBLENGPRODUCTATTRIBDETAIL A
WHERE A.ATTRIBNO = 'PCS/CCL' )aa on rr.productno=substr(aa.productno,1,length(aa.productno)-2)
where rr.productno='184H08027D245'

如果把条件改为 where aa.productno='184H08027D24500'
则显示的结果不同。
rr.productno显示的多出一条错误数据，而aa.productno显示的测试结果是正确的。我实在是不明白？？？

[susie8449]

不好意思，我现在知道原因了，我在截去productno的时候，有的末尾2位不一样的。还是谢谢了。

[chunyu.wu]

外连接:
除了显示匹配相等连接条件的数据外,还可以显示某一个表中无法匹配相等连接条件的记录!
------------------------------------------------
1) 左条件(+) = 右条件
左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也称为右外连接.
--------------------------------
可以用下列语句取代:
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;
2) 左条件 = 右条件(+)
右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也称为左外连接.
--------------------------------
可以用下列语句取代:
SELECT...FROM 表1 LEFT OUTER JOIN 表2 ON 条件;