sql小样例

问题：目前有主表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(+);