Oracle hierarchical SQL 查询应用.docx

1、Oracle hierarchical SQL 查询应用基本用法在Oracle中，有个很方便的特性，层次关系子句。其语法如下:hierarchical_query_clause:=START WITH condition CONNECT BY condition利用start with connect by 可以实现父子关系的查询，这是层次关系查询的基本用法。如下例：(SQL1.1)- SQL 1.1 - begin create table hier(parent varchar2(30),child varchar2(30);insert into hier values(null,Asi

2、a);insert into hier values(null,Australia);insert into hier values(null,Europe);insert into hier values(null,North America);insert into hier values(Asia,China);insert into hier values(Asia,Japan);insert into hier values(Australia,New South Wales);insert into hier values(New South Wales,Sydney);inser

3、t into hier values(California,Redwood Shores);insert into hier values(Canada,Ontario);insert into hier values(China,Beijing);insert into hier values(England,London);insert into hier values(Europe,United Kingdom);insert into hier values(Japan,Osaka);insert into hier values(Japan,Tokyo);insert into hi

4、er values(North America,Canada);insert into hier values(North America,USA);insert into hier values(Ontario,Ottawa);insert into hier values(Ontario,Toronto);insert into hier values(USA,California);insert into hier values(United Kingdom,England);end;那么我们可以使用START WITH . . . CONNECT BY . . . 子句将父级地区与孩子

5、地区连接起来，并将其层次等级显示出来。(SQL1.2) - SQL 1.2 -select level,child from hier start with parent is null connect by prior child = parent;LEVEL CHILD- -1 Asia2 China3 Beijing2 Japan3 Osaka3 Tokyo1 Australia2 New South Wales3 Sydney1 Europe2 United Kingdom3 England4 London1 North America2 Canada3 Ontario4 Ottawa

6、4 Toronto2 USA3 California4 Redwood Shores 自从Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：(SQL1.3)- SQL 1.3 -select level,sys_connect_by_path(child,/) path from hier start with parent is null connect by prior child = parent;LEVEL PATH - -1 /Asia2 /Asia/China3 /

7、Asia/China/Beijing2 /Asia/Japan3 /Asia/Japan/Osaka3 /Asia/Japan/Tokyo1 /Australia2 /Australia/New South Wales3 /Australia/New South Wales/Sydney1 /Europe2 /Europe/United Kingdom3 /Europe/United Kingdom/England4 /Europe/United Kingdom/England/London1 /North America2 /North America/Canada3 /North Amer

8、ica/Canada/Ontario4 /North America/Canada/Ontario/Ottawa4 /North America/Canada/Ontario/Toronto2 /North America/USA3 /North America/USA/California4 /North America/USA/California/Redwood Shores 除了实现基本父子关系应用查询，利用层次关系查询的特性还可以实现其他实际应用。应用例子:由航段组合出航线代码及航站ID计算应用场景在南航项目中，航线A-B-C-D-E代表起点为A,经停B/C/D,终点为E的一条航线。

9、可用以下有向图表示：由这些站点形成的航空运输路线，我们称之为航线。一条航线经过的站点至少有两个，即飞行起点（或称始发点）和飞行终点。在起点和终点之间可以有多个经停点。 航段通常是指能够构成旅客航程的航段，比如航线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

10、,54,55。 在南航记录中，同一个航线的航班代码是一致的。 而应用的环境则是，我们有的数据是航班代码，和各个航段。而航段用始发机场，到达机场表示。(origin,destination)。即我们有的数据样子如下:航班代码,始发,到达CZ3401,A,BCZ3401,A,CCZ3401,A,DCZ3401,A,ECZ3401,B,CCZ3401,B,DCZ3401,B,ECZ3401,C,DCZ3401,C,ECZ3401,D,E我们要根据这些数据组合出A-B-C-D-E航线代码出来，并计算出A,B,C,D,E的航站ID。数据准备我们先创建一个模拟数据表 表定义: (SQL2.1)- SQL

11、2.1 -create table BDMA_TEST_AIRLINE( AIRLINEID VARCHAR(6), ORIGIN VARCHAR2(3), DESTINATION VARCHAR2(3), ENABLED CHAR(1);-Enabled 数据是否生效;-AIRLINEID 航班,-ORIGIN 始发机场,-DESTINATION 到达机场,表数据:AIRLINEID ORIGIN DESTINATION ENABLEDCZ3401 CAN PEK 1CZ3401 CAN SHG 1CZ3401 CAN HBR 1CZ3401 CAN WUH 1CZ3401 CAN KUM

12、1CZ3401 SHG PEK 1CZ3401 SHG HBR 1CZ3401 SHG WUH 1CZ3401 SHG KUM 1CZ3401 PEK HBR 1CZ3401 PEK WUH 1CZ3401 PEK KUM 1CZ3401 HBR WUH 1CZ3401 HBR KUM 1CZ3401 WUH KUM 1CZ3402 WUH KUM 1CZ3402 KUM SHZ 1 其中Enabled是方便我们增加删除数据使用的。航站列表首先我们先求出各个航线的航站:(SQL2.2)- SQL 2.2 -select airlineid,origin from bdma_test_airli

13、ne where enabled=1 group by airlineid,originunion select airlineid,destination from bdma_test_airline where enabled=1 group by airlineid,destination结果:AIRLINEID ORIGINCZ3401 CANCZ3401 HBRCZ3401 KUMCZ3401 PEKCZ3401 SHGCZ3401 WUHCZ3402 KUMCZ3402 SHZCZ3402 WUH在这里我们可以看出CZ3401有6个航站，CZ3402有3个航站；航线然后我们根据航段

14、的始发和到达，将不同的航段组合起来，组合可能的航线。在这里组合规则是前一航段的到达是下一个航段的始发。这样我们可以列出组合出来航线代码(SQL2.3)：- SQL 2.3 -select airlineid,level LevelNum,SYS_CONNECT_BY_PATH(origin,-)|-|destination airline from (select airlineid,origin,destination from bdma_test_airline where enabled=1) connect by prior destination= origin and prior airlineid=airlineid;我们注意Connect by 子句: 是 prior destination= origin and prior airlineid=airlineid 连接的条件除了刚才所说的前一航段的到达是下一个航段的始发(prior destination= origin)，我们还要考虑航班的代码(prior airlineid=airlineid)，否则就会把不同的航班的航段都连接在一起。结果:AIRLINEID LEVELNUM AIRLINECZ3401 1 -C