不大看得懂你的题目,最好把标结构简单的说一下
可以写外联接
比如table1 和 table2如果他们有共同的字段id,而tabal2中有没有数据不能确定
那么可以写select *
from table1 LEFT OUTER JOIN table2
where table1.id = table2.id;
可以写外联接
比如table1 和 table2如果他们有共同的字段id,而tabal2中有没有数据不能确定
那么可以写select *
from table1 LEFT OUTER JOIN table2
where table1.id = table2.id;
a.*,
b.*,
c.*,
d.*
from
TABLE1 a,
TABLE2 b,
TABLE3 c,
TABLE4 d
where
a.SN =* b.SN and a.SN =* c.SN and a.SN =* d.SN
2 /ST_ID CYC_TIME CAPACITY OWNER MOVE_TIME
---------- ---------- ---------- --------------- ----------
GL2 10.23 8000 WP 1
IT 1 8000 HA 1
ET2I 1 8000 DF2 1
DF2I 1 8000 DF2 1
SM2 1 8000 SM 1選取了 5 列目前歷時: 00:00:00.47
SQL> SELECT * FROM BAO_TEMP
2 /ST_ID CYC_TIME CAPACITY OWNER MOVE_TIME
---------- ---------- ---------- --------------- ----------
GL2 10.23 8000 WP 1
IT 1 8000 HA 1
ET2I 1 8000 DF2 1
DF2I 1 8000 DF2 1
SM2 1 8000 SM 1
CuH 1 8000 CuH 1
DF1G 1 8000 DF1 1
TDR 1 8000 OS 1選取了 8 列目前歷時: 00:00:00.78--使用外連接--------------------------------------------------
SQL> SELECT A.ST_ID,A.CYC_TIME,B.ST_ID,B.CYC_TIME
2 FROM BAO_TEMP A,BAO_TEMP_1 B
3 WHERE A.ST_ID = B.ST_ID(+)
4 /ST_ID CYC_TIME ST_ID CYC_TIME
---------- ---------- ---------- ----------
CuH 1
DF1G 1
DF2I 1 DF2I 1
ET2I 1 ET2I 1
GL2 10.23 GL2 10.23
IT 1 IT 1
SM2 1 SM2 1
TDR 1選取了 8 列目前歷時: 00:00:00.62
在oracle 中应该
select * from table1,table2 where table1.sn=table2.sn(+);
SELECT
com_hsa.serial_num SN,
com_hsa.hd_pos HD_POS,
com_hsa.mr_res MR,
gld_hsa.gram_load GLD,
hal_hsa.hd_num wafercode,
sam_hsa.psa PSA
FROM (com_hsa INNER JOIN hal_hsa ON com_hsa.serial_num = hal_hsa.serial_num)
INNER JOIN sam_hsa
ON com_hsa.serial_num=sam_hsa.serial_num
and com_hsa.hd_pos=sam_hsa.hd_pos
INNER JOIN gld_hsa
ON com_hsa.serial_num = gld_hsa.serial_num
and com_hsa.serial_num=hal_hsa.serial_num
and com_hsa.hd_pos = hal_hsa.hd_pos
and com_hsa.hd_pos=gld_hsa.hd_pos
WHERE (hal_hsa.serial_num ='T525K5TPG')
order by com_hsa.hd_pos asc
那就是oracle的語法,可以看見我是在sql plus下測試的!
com_hsa.hd_pos HD_POS ,
com_hsa.mr_res MR ,
gld_hsa.gram_load GLD ,
hal_hsa.hd_num wafercode ,
sam_hsa.psa PSA
FROM com_hsa,gld_hsa,hal_hsa,sam_hsa
WHERE com_hsa.serial_num = hal_hsa.serial_num(+)
AND com_hsa.serial_num = gld_hsa.serial_num(+)
AND com_hsa.serial_num = hal_hsa.serial_num(+)
AND com_hsa.hd_pos = hal_hsa.hd_pos(+)
AND com_hsa.hd_pos = gld_hsa.hd_pos(+)
AND hal_hsa.serial_num = 'T525K5TPG'
order by com_hsa.hd_pos asc
SELECT a.aa,
b.bb,
c.cc,
d.dd
FROM TABLE1 a,TABLE2 b,TABLE3 c,TABLE4 d
WHERE a.sn = '12345'
and a.sn = b.sn(+)
and a.sn = c.sn(+)
and a.sn = d.sn(+)
baojianjun(包子) 能帮我解释一下么?
AND com_hsa.hd_pos = gld_hsa.hd_pos
我觉得是这两个限制条件后不该加外联(+)