--现有如下5个表,关联关系为:table_3引用table_1和table_2,table_5引用table_4,table_4引用table_1
create table TABLE_1
(
TABLE_1_COL_1 NUMBER(3),
TABLE_1_COL_2 NVARCHAR2(20)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table TABLE_2
(
TABLE_2_COL_1 NUMBER(3),
TABLE_2_COL_2 NVARCHAR2(20)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table TABLE_3
(
TABLE_3_COL_1 NUMBER(3),
TABLE_3_COL_2 NVARCHAR2(20),
TABLE_3_REF_1 NUMBER(3),
TABLE_3_REF_2 NUMBER(3)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table TABLE_4
(
TABLE_4_COL_1 NUMBER(3),
TABLE_4_COL_2 NVARCHAR2(20),
TABLE_4_REF_1 NUMBER(3)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table TABLE_5
(
TABLE_5_COL_1 NUMBER(3),
TABLE_5_COL_2 NVARCHAR2(20),
TABLE_5_REF_4 NUMBER(3)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
--table_5引用table_4,table4引用table_1,如何在table_5有数据,table_1没有数据,且table_4没有数据的情况下,列出的查询结果中包含table_4和table_1的字段
select layer_1.table_5_col_1 as 记录需号,layer_1.table_5_col_2 as 基础数据,
layer_2.table_4_col_2 as 直接引用值,layer_3.table_1_col_2 as 间接引用值
from table_5 layer_1,table_4 layer_2,table_1 layer_3
where layer_3.table_1_col_1(+) = layer_2.table_4_ref_1
and layer_1.table_5_ref_4 = layer_2.table_4_col_1(+)
--table_3引用table_1和table_2,如何在table_3有数据,table_1或table_2没有数据的情况下,列出的查询结果中包含table_1和table_2的字段
select table_3_col_1 ,table_3_col_2 ,table_1_col_1 ,table_1_col_2 ,table_2_col_2 from table_1 , table_2 ,table_3
where table_3_ref_1 = table_1_col_1 (+)
and table_3_ref_2 = table_2_col_1 (+)