我想在Oracle 8i中加入四个表。这四个表名称是用户,挑选,petaways和重新定位。四个表中的每一个都有一个User_ID字段。挑选,占地和重新定位的外观:
| USER_ID | PICKS | PICK_VOLUME |
+---------+-------+-------------+
| SMITH12 | 234 | 2431.12 |
ptaways和重新定位桌子外观相同,除了" picks"one_answers" pick_volume"分别用ptaways和搬迁代替。
我想做的左JON上的表是用户表,因为它将包含所有用户_ids,而不管它们是否仅选择库存并且没有搬迁或将其放弃。换句话说,即使某些用户只有重新定位,而不是选择,petaways和lococations或其任何组合,我也希望看到所有记录。
用户表看起来像这样:
| USER_ID | SHIFT | GROUP_ID |
+---------+-------+------------+
| SMITH12 | NIGHT | STOCK_KEEP |
非常理想的是,我想在结果的左侧将这三列与挑选,petaways和Rockotates一起放置在右侧。最终选择结果应该看起来像:
| USER_ID | SHIFT | GROUP_ID | PICKS | PICK_VOL | RELOCATES | RELOCATE_VOL | PUTAWAYS | PUTAWAY_VOL |
+---------+-------+------------+-------+----------+-----------+--------------+----------+-------------+
| SMITH12 | NIGHT | STOCK_KEEP | 234 | 2431.12 | NULL | NULL | 4 | 76.52 |
我尝试了此SQL查询:
select users.user_id, users.group_id, users.shift, pi."Picks", pi."Pick Volume", pu."Putaways", pu."Putaway Volume", re."Relocates", re."Relocate Volume"
from users, pi, pu, re
where users.user_id = pi.user_id
and users.user_id = pu.user_id
and users.user_id = re.user_id;
,但它仅返回用户在选拔,重新定位和petaway表中至少进行一项交易的行,而行则返回。这是可以预料的,因为我正在做香草。我假设我需要某种剩余的加入,但是我不确定如何在Oracle 8i中写下它。
使用( )运算符:
select users.user_id, users.group_id, users.shift, pi."Picks", pi."Pick Volume", pu."Putaways", pu."Putaway Volume", re."Relocates", re."Relocate Volume"
from users, pi, pu, re
where users.user_id = pi.user_id(+)
and users.user_id = pu.user_id(+)
and users.user_id = re.user_id(+);
http://docs.oracle.com/cd/b28359_01/server.111/b28286/queries006.htm
您可以使用"左Out ofer Join":
select users.user_id
, users.group_id
, users.shift
, pi."Picks"
, pi."Pick Volume"
, pu."Putaways"
, pu."Putaway Volume"
, re."Relocates"
, re."Relocate Volume"
from users
LEFT OUTER JOIN pi
ON users.user_id = pi.user_id
LEFT OUTER JOIN pu
ON users.user_id = pu.user_id
LEFT OUTER JOIN re
ON users.user_id = pi.user_id
;