Oracle hierarchical SQL 查询应用.docx
《Oracle hierarchical SQL 查询应用.docx》由会员分享，可在线阅读，更多相关《Oracle hierarchical SQL 查询应用.docx（17页珍藏版）》请在冰豆网上搜索。
OraclehierarchicalSQL查询应用
基本用法
在Oracle中，有个很方便的特性，层次关系子句。
其语法如下:
hierarchical_query_clause:
:
=[STARTWITHcondition]CONNECTBYcondition
利用startwith…connectby…可以实现父子关系的查询，这是层次关系查询的基本用法。
如下例：
（SQL1.1）
--SQL1.1--
begin
createtablehier
（
parentvarchar2（30）,
childvarchar2（30）
）;
insertintohiervalues（null,'Asia'）;
insertintohiervalues（null,'Australia'）;
insertintohiervalues（null,'Europe'）;
insertintohiervalues（null,'NorthAmerica'）;
insertintohiervalues（'Asia','China'）;
insertintohiervalues（'Asia','Japan'）;
insertintohiervalues（'Australia','NewSouthWales'）;
insertintohiervalues（'NewSouthWales','Sydney'）;
insertintohiervalues（'California','RedwoodShores'）;
insertintohiervalues（'Canada','Ontario'）;
insertintohiervalues（'China','Beijing'）;
insertintohiervalues（'England','London'）;
insertintohiervalues（'Europe','UnitedKingdom'）;
insertintohiervalues（'Japan','Osaka'）;
insertintohiervalues（'Japan','Tokyo'）;
insertintohiervalues（'NorthAmerica','Canada'）;
insertintohiervalues（'NorthAmerica','USA'）;
insertintohiervalues（'Ontario','Ottawa'）;
insertintohiervalues（'Ontario','Toronto'）;
insertintohiervalues（'USA','California'）;
insertintohiervalues（'UnitedKingdom','England'）;
end;
那么我们可以使用STARTWITH...CONNECTBY...子句将父级地区与孩子地区连接起来，并将其层次等级显示出来。
（SQL1.2）
--SQL1.2--
selectlevel,childfromhierstartwithparentisnullconnectbypriorchild=parent;
LEVELCHILD
------------------------------------
1Asia
2China
3Beijing
2Japan
3Osaka
3Tokyo
1Australia
2NewSouthWales
3Sydney
1Europe
2UnitedKingdom
3England
4London
1NorthAmerica
2Canada
3Ontario
4Ottawa
4Toronto
2USA
3California
4RedwoodShores
自从Oracle9i开始，就可以通过SYS_CONNECT_BY_PATH函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
如下例所示：
（SQL1.3）
--SQL1.3--
selectlevel,sys_connect_by_path（child,'/'）pathfromhierstartwithparentisnullconnectbypriorchild=parent;
LEVELPATH
----------------------------------------------------
1/Asia
2/Asia/China
3/Asia/China/Beijing
2/Asia/Japan
3/Asia/Japan/Osaka
3/Asia/Japan/Tokyo
1/Australia
2/Australia/NewSouthWales
3/Australia/NewSouthWales/Sydney
1/Europe
2/Europe/UnitedKingdom
3/Europe/UnitedKingdom/England
4/Europe/UnitedKingdom/England/London
1/NorthAmerica
2/NorthAmerica/Canada
3/NorthAmerica/Canada/Ontario
4/NorthAmerica/Canada/Ontario/Ottawa
4/NorthAmerica/Canada/Ontario/Toronto
2/NorthAmerica/USA
3/NorthAmerica/USA/California
4/NorthAmerica/USA/California/RedwoodShores
除了实现基本父子关系应用查询，利用层次关系查询的特性还可以实现其他实际应用。
应用例子:
由航段组合出航线代码及航站ID计算
应用场景
在南航项目中，航线A-B-C-D-E代表起点为A,经停B/C/D,终点为E的一条航线。
可用以下有向图表示：
由这些站点形成的航空运输路线，我们称之为航线。
一条航线经过的站点至少有两个，即飞行起点（或称始发点）和飞行终点。
在起点和终点之间可以有多个经停点。
航段通常是指能够构成旅客航程的航段，比如航线A-B-C-D-E，那么航段有10中组合:
A-B,A-C,A-D,A-E,B-C,B-D,B-E,C-D,C-E,D-E。
可以简单的证明，一个航线有n个航站（A-B-C-D-E航线的航站数是5）,那么该航线的航段数是n*（n-1）/2。
而在南航应用中，航线中的航站ID等于航站数n*10+m（m是航站的序号）。
在航线A-B-C-D-E中，A,B,C,D,E的航站ID分别为51,52,53,54,55。
在南航记录中，同一个航线的航班代码是一致的。
而应用的环境则是，我们有的数据是航班代码，和各个航段。
而航段用始发机场，到达机场表示。
（origin,destination）。
即我们有的数据样子如下:
航班代码,始发,到达
CZ3401,A,B
CZ3401,A,C
CZ3401,A,D
CZ3401,A,E
CZ3401,B,C
CZ3401,B,D
CZ3401,B,E
CZ3401,C,D
CZ3401,C,E
CZ3401,D,E
我们要根据这些数据组合出A-B-C-D-E航线代码出来，并计算出A,B,C,D,E的航站ID。
数据准备
我们先创建一个模拟数据表
表定义:
（SQL2.1）
--SQL2.1--
createtableBDMA_TEST_AIRLINE
（
AIRLINEIDVARCHAR（6）,
ORIGINVARCHAR2（3）,
DESTINATIONVARCHAR2（3）,
ENABLEDCHAR
（1）
）;
--Enabled数据是否生效;
--AIRLINEID航班,
--ORIGIN始发机场,
--DESTINATION到达机场,
表数据:
AIRLINEIDORIGINDESTINATIONENABLED
CZ3401CANPEK1
CZ3401CANSHG1
CZ3401CANHBR1
CZ3401CANWUH1
CZ3401CANKUM1
CZ3401SHGPEK1
CZ3401SHGHBR1
CZ3401SHGWUH1
CZ3401SHGKUM1
CZ3401PEKHBR1
CZ3401PEKWUH1
CZ3401PEKKUM1
CZ3401HBRWUH1
CZ3401HBRKUM1
CZ3401WUHKUM1
CZ3402WUHKUM1
CZ3402KUMSHZ1
其中Enabled是方便我们增加删除数据使用的。
航站列表
首先我们先求出各个航线的航站:
（SQL2.2）
--SQL2.2--
selectairlineid,originfrombdma_test_airlinewhereenabled='1'groupbyairlineid,origin
union
selectairlineid,destinationfrombdma_test_airlinewhereenabled='1'groupbyairlineid,destination
结果:
AIRLINEIDORIGIN
CZ3401CAN
CZ3401HBR
CZ3401KUM
CZ3401PEK
CZ3401SHG
CZ3401WUH
CZ3402KUM
CZ3402SHZ
CZ3402WUH
在这里我们可以看出CZ3401有6个航站，CZ3402有3个航站；
航线
然后我们根据航段的始发和到达，将不同的航段组合起来，组合可能的航线。
在这里组合规则是前一航段的到达是下一个航段的始发。
这样我们可以列出组合出来航线代码（SQL2.3）：
--SQL2.3--
selectairlineid,levelLevelNum,SYS_CONNECT_BY_PATH（origin,'-'）||'-'||destinationairline
from（selectairlineid,origin,destinationfrombdma_test_airlinewhereenabled='1'）
connectbypriordestination=originandpriorairlineid=airlineid;
我们注意Connectby子句:
是priordestination=originandpriorairlineid=airlineid
连接的条件除了刚才所说的前一航段的到达是下一个航段的始发（priordestination=origin），我们还要考虑航班的代码（priorairlineid=airlineid），否则就会把不同的航班的航段都连接在一起。
结果:
AIRLINEIDLEVELNUMAIRLINE
CZ34011-C
升级会员 