Oracle 9i 一对多的关系查询

Question

一对多关系查询表:
ea,eb,ga,gb
表关系如下
ea1=eb1
ga1=gb1
eb1=gb31 and eb3 = gb33
事例数据及关系如下:SELECT ea1, eb3, gb1, gb3, gb31, gb33
  FROM (SELECT 'G1' gb1, '1' gb3, 'E1' gb31, '1' gb33
          FROM dual
        UNION ALL
        SELECT 'G1', '2', 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'G2', '3', 'E2', '3'
          FROM dual
        UNION ALL
        SELECT 'G1', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '6', 'E2', '6'
          FROM dual
        UNION ALL
        SELECT 'G3', '7', 'E2', '7'
          FROM dual
        UNION ALL
        SELECT 'G4', '8', 'E2', '8' FROM dual) gb,
       (SELECT 'G1' ga1
          FROM dual
        UNION ALL
        SELECT 'G2'
          FROM dual
        UNION ALL
        SELECT 'G3' FROM dual) ga,
       (SELECT 'E1' eb1, '1' eb3
          FROM dual
        UNION ALL
        SELECT 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'E1', '4'
          FROM dual
        UNION ALL
        SELECT 'E1', '5' FROM dual) eb,
       (SELECT 'E1' ea1 FROM dual) ea
 WHERE ea1 = eb1
   AND ga1 = gb1
   AND eb1 = gb31
   AND eb3 = gb33
   AND ea1 = 'E1'
 ORDER BY ea1, eb3, gb1, gb3执行结果:    EA1 EB3 GB1 GB3 GB31 GB33
1 E1 1 G1 1 E1 1
2 E1 2 G1 2 E1 2
3 E1 3 G1 4 E1 3
4 E1 3 G2 4 E1 3期望结果:    EA1 EB3 GB1 GB3 GB31 GB33
1 E1 1 G1 1 E1 1
2 E1 2 G1 2 E1 2
3 E1 3 G1 4 E1 3
4 E1 3 G2 4 E1 3
5 E1 4
6 E1 5SQL应该如何去修改,才能得到上面的结果

Answer 1

WHERE ea1 = eb1改成
WHERE ea1 = eb1(+)

Answer 2

结果还是:
    EA1 EB3 GB1 GB3 GB31 GB33
1 E1 1 G1 1 E1 1
2 E1 2 G1 2 E1 2
3 E1 3 G1 4 E1 3
4 E1 3 G2 4 E1 3

Answer 3

看错了
SELECT ea1, eb3, gb1, gb3, gb31, gb33
  FROM (SELECT 'G1' gb1, '1' gb3, 'E1' gb31, '1' gb33
          FROM dual
        UNION ALL
        SELECT 'G1', '2', 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'G2', '3', 'E2', '3'
          FROM dual
        UNION ALL
        SELECT 'G1', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '6', 'E2', '6'
          FROM dual
        UNION ALL
        SELECT 'G3', '7', 'E2', '7'
          FROM dual
        UNION ALL
        SELECT 'G4', '8', 'E2', '8' FROM dual) gb
    inner join
       (SELECT 'G1' ga1
          FROM dual
        UNION ALL
        SELECT 'G2'
          FROM dual
        UNION ALL
        SELECT 'G3' FROM dual) ga
    on ga1 = gb1
    right join
       (SELECT 'E1' eb1, '1' eb3
          FROM dual
        UNION ALL
        SELECT 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'E1', '4'
          FROM dual
        UNION ALL
        SELECT 'E1', '5' FROM dual) eb
    on eb1 = gb31 AND eb3 = gb33
    left join
       (SELECT 'E1' ea1 FROM dual) ea
    on ea1 = eb1
 ORDER BY ea1, eb3, gb1, gb3

Answer 4

SQL> 
SQL> select ea.ea1,eb.eb3,gb.* from eb,gb,ea
  2  where eb.eb1=gb.gb31(+) and eb.eb3=gb.gb33(+) and eb.eb1=ea.ea1
  3  /
 
EA1 EB3 GB1 GB3 GB31 GB33
--- --- --- --- ---- ----
E1  1   G1  1   E1   1
E1  2   G1  2   E1   2
E1  3   G1  4   E1   3
E1  3   G2  4   E1   3
E1  4                
E1  5                
 
6 rows selected
 
SQL> 

Answer 5

上面这个没有引用ga这个表，下面这个吧GA这个表也加进去
SQL> select ea.ea1, eb.eb3, gb.*
  2    from eb, gb, ea, ga
  3   where eb.eb1 = gb.gb31(+)
  4     and eb.eb3 = gb.gb33(+)
  5     and eb.eb1 = ea.ea1
  6     and gb.gb1 = ga.ga1(+)
  7   order by 1, 2
  8  /
 
EA1 EB3 GB1 GB3 GB31 GB33
--- --- --- --- ---- ----
E1  1   G1  1   E1   1
E1  2   G1  2   E1   2
E1  3   G2  4   E1   3
E1  3   G1  4   E1   3
E1  4                
E1  5                
 
6 rows selected

Answer 6

select ea.ea1,eb.eb3,gb.*
from 
ea left outer join eb on ea1=eb1 
left outer join gb oneb1=gb31 and eb3 = gb33 
inner join ga on ga1=gb1 

Answer 7

数据源的引用可以用with table_name as (query statement)
with gb as (SELECT 'G1' gb1, '1' gb3, 'E1' gb31, '1' gb33
          FROM dual
        UNION ALL
        SELECT 'G1', '2', 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'G2', '3', 'E2', '3'
          FROM dual
        UNION ALL
        SELECT 'G1', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '6', 'E2', '6'
          FROM dual
        UNION ALL
        SELECT 'G3', '7', 'E2', '7'
          FROM dual
        UNION ALL
        SELECT 'G4', '8', 'E2', '8' FROM dual) ,
 ga as (SELECT 'G1' ga1
          FROM dual
        UNION ALL
        SELECT 'G2'
          FROM dual
        UNION ALL
        SELECT 'G3' FROM dual),
  eb as (SELECT 'E1' eb1, '1' eb3
          FROM dual
        UNION ALL
        SELECT 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'E1', '4'
          FROM dual
        UNION ALL
        SELECT 'E1', '5' FROM dual),
  ea as (SELECT 'E1' ea1 FROM dual)
select ea.ea1, eb.eb3, gb.*
  from eb, gb, ea, ga
 where eb.eb1 = gb.gb31(+)
   and eb.eb3 = gb.gb33(+)
   and eb.eb1 = ea.ea1
   and gb.gb1 = ga.ga1(+)
 order by 1, 2

Answer 8

with gb as (SELECT 'G1' gb1, '1' gb3, 'E1' gb31, '1' gb33
          FROM dual
        UNION ALL
        SELECT 'G1', '2', 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'G2', '3', 'E2', '3'
          FROM dual
        UNION ALL
        SELECT 'G1', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '6', 'E2', '6'
          FROM dual
        UNION ALL
        SELECT 'G3', '7', 'E2', '7'
          FROM dual
        UNION ALL
        SELECT 'G4', '8', 'E2', '8' FROM dual) ,
 ga as (SELECT 'G1' ga1
          FROM dual
        UNION ALL
        SELECT 'G2'
          FROM dual
        UNION ALL
        SELECT 'G3' FROM dual),
  eb as (SELECT 'E1' eb1, '1' eb3
          FROM dual
        UNION ALL
        SELECT 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'E1', '4'
          FROM dual
        UNION ALL
        SELECT 'E1', '5' FROM dual),
  ea as (SELECT 'E1' ea1 FROM dual)
select ea.ea1,eb.eb3,gb.*
from 
ea left outer join eb on ea1=eb1 
left outer join gb on eb1=gb31 and eb3 = gb33 
left join ga on ga1=gb1 --RESULT:E1 3 G1 4 E1 3
E1 2 G1 2 E1 2
E1 1 G1 1 E1 1
E1 3 G2 4 E1 3
E1 5
E1 4

Answer 9

風一樣的男子,谢谢!结果已经正确了,但原ga表中还有一字段gaconf,条件为gaconf='Y'
加入这个条件后,结果就不对了,看是否有办法解决

Answer 10

with gb as (SELECT 'G1' gb1, '1' gb3, 'E1' gb31, '1' gb33
          FROM dual
        UNION ALL
        SELECT 'G1', '2', 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'G2', '3', 'E2', '3'
          FROM dual
        UNION ALL
        SELECT 'G1', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '4', 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'G2', '6', 'E2', '6'
          FROM dual
        UNION ALL
        SELECT 'G3', '7', 'E2', '7'
          FROM dual
        UNION ALL
        SELECT 'G4', '8', 'E2', '8' FROM dual) ,
 ga as (SELECT 'G1' ga1,'Y' gaconf
          FROM dual
        UNION ALL
        SELECT 'G2','N'
          FROM dual
        UNION ALL
        SELECT 'G3','N' FROM dual),
  eb as (SELECT 'E1' eb1, '1' eb3
          FROM dual
        UNION ALL
        SELECT 'E1', '2'
          FROM dual
        UNION ALL
        SELECT 'E1', '3'
          FROM dual
        UNION ALL
        SELECT 'E1', '4'
          FROM dual
        UNION ALL
        SELECT 'E1', '5' FROM dual),
  ea as (SELECT 'E1' ea1 FROM dual)
select ea.ea1,eb.eb3,gb.*
from 
ea left outer join eb on ea1=eb1 
left outer join gb on eb1=gb31 and eb3 = gb33 
left join (select * from ga where gaconf='Y') on ga1=gb1 
order by 1,2

Answer 11

select ea.ea1, eb.eb3, gb.*
  from eb, gb, ea, ga
 where eb.eb1 = gb.gb31(+)
   and eb.eb3 = gb.gb33(+)
   and eb.eb1 = ea.ea1
   and gb.gb1 = ga.ga1(+)
   and ga.gaconf(+)='y'
 order by 1, 2

Answer 12

非常感谢 fxianxian,cosio测试通过！