问题:目前有主表main,三个子表table1,table2,table3
通过外键main_id一对一关联,main表的一个字段type,type=1,表示类型为子表1,type=2表示类型为子表2,type=3表示类型为子表3
需要读取所有主表的记录,显示的属性为type和name(分别为子表的属性)
with A as (select main_id,max(name) name,max(type) type from table1 group by main_id),
B as (select main_id,max(name) name,max(type) type from table2 group by main_id),
C as (select main_id,max(name) name,max(type) type from table3 group by main_id)
select decode(M.type,1,A.name,2,B.name,3,C.name) name,
decode(M.type,1,A.type,2,B.type,3,C.type) type
from main M, A, B
where M.main_id = A.main_id(+) and M.main_id = B.main_id(+) and M.main_id = C.main_id(+);