2015.7.24 CAD库中列举五字代码点所属航路及终端区图，左连接的累加
select decode(fb.tupr,null,'仅航路',decode(fc.aw,null,'仅终端区','航路及终端区')) 范围,pt 五字代码点,fb.tupr 终端区图及程序,fc.aw 航路 from
(
select distinct wz pt from
(
select wz,tupr,0 from
(select f2.code_fix_point wz,min(f1.tu) tupr from
(
select sid_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '离场' tp from sid
union
select star_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '进场' tp from star
union
select iap_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '进近' tp from iap
) f1, procedure_leg f2
where
(
(f1.id =f2.iap_id and f1.tp='进近') or
(f1.id=f2.sid_id and f1.tp='离场') or
(f1.id=f2.star_id and f1.tp='进场')
)
and f2.code_type_fix_point= 'DESIGNATED_POINT' and length(REGEXP_REPLACE(f2.code_fix_point, '[0-9]',''))=5
group by f2.code_fix_point order by wz
)
union
select wz,aw,1 from
(
select substr(airway_point_name,instr(airway_point_name,'/')+1) wz,max(airway_identifier ) aw from AIRWAY_ORDERED_POINT
where chart_type_id =3 and
airway_point_type =11 and
instr(airway_identifier,'XX')=0 and
length(REGEXP_REPLACE(substr(airway_point_name,instr(airway_point_name,'/')+1), '[0-9]',''))=5
group by substr(airway_point_name,instr(airway_point_name,'/')+1)
)
)
)
left join
(
select wz,tupr,0 from
(
select f2.code_fix_point wz,min(f1.tu) tupr from
(
select sid_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '离场' tp from sid
union
select star_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '进场' tp from star
union
select iap_id id, substr(upper(decode(length(REGEXP_REPLACE(txt_rmk, '[^-]','')),1,txt_rmk,code_icao||'-'||txt_rmk)),1,7) || ' ' || upper(txt_desig) tu, '进近' tp from iap
) f1, procedure_leg f2
where
(
(f1.id =f2.iap_id and f1.tp='进近') or
(f1.id=f2.sid_id and f1.tp='离场') or
(f1.id=f2.star_id and f1.tp='进场')
)
and f2.code_type_fix_point= 'DESIGNATED_POINT' and length(REGEXP_REPLACE(f2.code_fix_point, '[0-9]',''))=5
group by f2.code_fix_point order by wz
)
) fb
on pt=fb.wz
left join
(
select wz,aw,1 from
(
select substr(airway_point_name,instr(airway_point_name,'/')+1) wz,max(airway_identifier ) aw from AIRWAY_ORDERED_POINT
where chart_type_id =3 and
airway_point_type =11 and
instr(airway_identifier,'XX')=0 and
length(REGEXP_REPLACE(substr(airway_point_name,instr(airway_point_name,'/')+1), '[0-9]',''))=5
group by substr(airway_point_name,instr(airway_point_name,'/')+1)
)
)fc
on pt=fc.wz
order by decode(fb.tupr,null,2,decode(fc.aw,null,3,1))
最新文章
- 回归树|GBDT|Gradient Boosting|Gradient Boosting Classifier
- sql join用法学习
- php函数的传值如果需要引用传递注意的细节
- Java连接池详解
- HDU 2098 分拆素数和
- (转)Maven实战（二）构建简单Maven项目
- mysql Fatal error encountered during command execution
- BZOJ 1050: [HAOI2006]旅行comf (并查集 或 单调队列)
- JSP自定义标签就是如此简单
- [BZOJ1657] [Usaco2006 Mar] Mooo 奶牛的歌声 (单调栈)
- 移动端，input、textarea滚动至可视区域
- 【Spark篇】---Spark中广播变量和累加器
- web前端之Javascript---function函数
- Cannot make a static reference to the non-static
- nginx——Nginx 防爬虫优化
- 【JVM.11】Java内存模型与线程
- sublime text 3-right click context menu
- 使用vuex保存singer每个歌星的基本信息
- oracle客户端plsql安装
- [bash] 显示配色