left join的多重串联与groupby
有三张表或组合查询，f1,f2,f3，其中，f1分别与f2,f3是一对多关系，f1一条记录可能对应f2或f3中0条或多条记录
要创建一个查询，以f1为基准，即f1中有多少条记录，结果也就返回对应数量的记录，并整合f2，f3中的相应信息
此时需要创建两个left join 串接并加上group by如：
有三张表或组合查询，f1,f2,f3，其中，f1分别与f2,f3是一对多关系，f1一条记录可能对应f2或f3中0条或多条记录
要创建一个查询，以f1为基准，即f1中有多少条记录，结果也就返回对应数量的记录，并整合f2，f3中的相应信息
此时需要创建两个left join 串接并加上group by如：
select f1.x,f1.y,max(f2.b),wmsys.wm_concat(f3.c)
from
(.........
) f1
left join
(........
) f2
on f1.z=f2.k
left join
(........
) f3
on f1.z=f3.c
group by f1.x,f1.y
实例
select f1.pro_uuid,f1.程序类型,f1.程序名,f1.过渡名,f1.航线类型,f1.航线限定1,f1.航线限定2, f1.机型限制
,wmsys.wm_concat(nvl2(f2.page,f1.ad||'-'||f2.page,null)) 航图, wmsys.wm_concat(f2.nav_source) 导航源
,wmsys.wm_concat(distinct nvl2(f3.rrwd,'RW'||f3.rrwd,null)) 跑道
from
(
select d.procedure_uuid pro_uuid,a.designator ad,
decode(d.PRO_TYPE,'SID','离场','STAR','进场','IAP','进近',PRO_TYPE) 程序类型,
d.PRO_NAME 程序名,
d.TRANS_IDEN 过渡名,
d.route_type||' '||(select name from TERMPRO_RULE_ROUTE_TYPE where protype=d.pro_type and routetype=d.route_type) 航线类型,
d.qulifier_1||' '||(select QUALIFIERNAME from TERMPRO_RULE_ROUTE_QUALIFIER where QUALIFIER=d.qulifier_1 and ONE_OR_TWO=1) 航线限定1,
d.qulifier_2||' '||(select QUALIFIERNAME from TERMPRO_RULE_ROUTE_QUALIFIER where QUALIFIER=d.qulifier_2 and ONE_OR_TWO=2) 航线限定2,
decode(acft_cat,'P',null,(select name from RULE_ACFT_CAT where CODE_ACFT_CAT= d.acft_cat)) 机型限制
from TERMINAL_PROCEDURE_TS d,airport_heliport_ts a
where d.airport_heliport_uuid='4a4ea52d-69be-48e4-baa1-321ee6d023d7'
and d.airport_heliport_uuid=a.airport_heliport_uuid
and vernumber(a.eff_version_id)<=201799 and vernumber(a.out_version_id)>201799 and d.interpretation='BASELINE'
and vernumber(d.eff_version_id)<=201799 and vernumber(d.out_version_id)>201799 and d.interpretation='BASELINE'
) f1
left join
(
select pc.procedure_uuid pro_uuid, c.page_number page, tc.type_name nav_source
from terminal_chart_ts c, termpro_chart_ts pc, terminal_chart_type tc
where pc.chart_uuid=c.terminal_chart_uuid
and c.sub_chart_type_id=tc.chart_type_id
and vernumber(c.eff_version_id)<=201799 and vernumber(c.out_version_id)>201799 and c.interpretation='BASELINE'
and vernumber(pc.eff_version_id)<=201799 and vernumber(pc.out_version_id)>201799 and pc.interpretation='BASELINE'
) f2
on f1.pro_uuid=f2.pro_uuid
left join
(
select pw.procedure_uuid pro_uuid, rd.designator rrwd
from TERMPRO_RWYDRC_TS pw, runway_direction_ts rd
where pw.runway_direction_uuid=rd.runway_direction_uuid
and vernumber(pw.eff_version_id)<=201799 and vernumber(pw.out_version_id)>201799 and pw.interpretation='BASELINE'
and vernumber(rd.eff_version_id)<=201799 and vernumber(rd.out_version_id)>201799 and rd.interpretation='BASELINE'
) f3
on f1.pro_uuid=f3.pro_uuid
group by f1.pro_uuid,f1.程序类型,f1.程序名,f1.过渡名,f1.航线类型,f1.航线限定1,f1.航线限定2, f1.机型限制
order by decode(f1.程序类型,'离场',1,'进场',2,'进近',3),f1.程序名,f1.过渡名
