SQL / Oracle Server,完全外部联接和3个或更多表

我有点问题 .

我在Sql服务器中使用全外连接 - 到目前为止它可以工作,但现在我添加了一个表,它不能正常工作 .

这是我的代码:

SELECT *
FROM Table1 h , (db1..Table2 s FULL OUTER JOIN  db1..Table3 k
on k.attributT3_1 = s.attributT2_1 
    and left(k.attributT3_2,4) = year(s.attributT2_2)
    and substring(k.attributT3_2,6,1) = s.attributT2_2
    and (case when k.attributT3_3 = 0 and k.attributT3_4 = 11 then 10 
        when k.attributT3_3 = 0 and k.attributT3_4 = 14 then 40
        when k.attributT3_3 = 0 and k.attributT3_4 = 16 then 60 
        when k.attributT3_3 = 0 and k.attributT3_4 = 90 then 10 
        when k.attributT3_3 = 1 and k.attributT3_4 = 11 then 11 
        when k.attributT3_3 = 2 and k.attributT3_4 = 11 then 12 
        when k.attributT3_3 = 4 and k.attributT3_4 = 11 then 14 
        when k.attributT3_3 = 7 and k.attributT3_4 = 11 then 17 
        else k.attributT3_3 end) = s.attributT2_3) 
where h.attributT1_1 = k.attributT3_1 
and s.attributT3_1 = '  260585'
and h.attributT1_2 = 055

这是我的SQL,可能有点混乱^^

我的问题是FULL OUTER JOIN不起作用,因为我添加了Table1,因为我需要一些信息,现在FULL OUTER JOIN就像LEFT OUTER JOIN一样工作 .

我也可能需要在此FULL OUTER JOIN中添加一个或多个表 .

有谁知道我怎么能让它工作? (是的,我知道我可以使用2个左外连接和一个联合使其工作,但这不是我想要做的)

顺便说一下脚本也可以在oracle上工作:D

输出应该是这样的:http://img402.imageshack.us/img402/4618/bildwq.jpg

Column1 = Table1 Column2 = Table2 Column3 = Table3

表中不存在空值

我的脚本做同样的事情,除了你不能看到第2列中的行与空值

回答(1)

2 years ago

WHERE子句限制了可以返回的结果:

where h.attributT1_1 = k.attributT3_1 
and s.attributT3_1 = '  260585'
and h.attributT1_2 = 055

这意味着只显示表H,K和S贡献一行(非空)的结果 . 实际上,您已从联接中删除了OUTER . 尝试将这些条件移动到连接定义中 .

举例说明我的意思:

SQL> create table t1 (id integer, text varchar2(10));
SQL> create table t2 (id integer, text varchar2(10));
SQL> insert into t1 values (1, 'text');
SQL> insert into t1 values (2, 'text');
SQL> insert into t2 values (2, 'text');
SQL> insert into t2 values (3, 'text');
SQL> commit;

完整的外部联接:

SQL> select * from t1 full outer join t2 on t1.id = t2.id;

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text
                               3 text
         1 text

现在使用WHERE子句中的过滤器(请注意,2个记录消失,就好像“full outer”不存在一样):

1* select * from t1 full outer join t2 on t1.id = t2.id
  2* where t1.text='text'
  3  and t2.text='text';

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text

现在将过滤器移入连接:

SQL> select * from t1 full outer join t2 on t1.id = t2.id
  2                                      and t1.text='text'
  3                                      and t2.text='text';

        ID TEXT               ID TEXT
---------- ---------- ---------- ----------
         2 text                2 text
                               3 text
         1 text