查询一对多 oracle,Oracle 9i 一对多的关系查询

一对多关系查询

表:

ea,eb,ga,gb

表关系如下

ea1=eb1

ga1=gb1

eb1=gb31 and eb3 = gb33

事例数据及关系如下:

SQL code:

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