推荐订阅源

近日遇到一问题，即根据航班航程信息生成航班的航段视图，其中航程数据是类似于 AAA,BBB,CCC,DDD 的字符串，
按照飞机的飞行路线，每两个机场均可以形成一个航段，故此例中，应该有以下航段:
AAA-BBB
AAA-CCC
AAA-DDD
BBB-CCC
BBB-DDD
CCC-DDD
如果有n段航程，则应该有 n*(n-1)/2 个航段，经过CSDN网友的指点，现总结如下,供大家分享:
--这句是技巧所在
--SELECT ROWNUM RN FROM ALL_OBJECTS WHERE ROWNUM <= 5 
--drop table T_TEST;
create table T_TEST
(
  CARRIERID VARCHAR2(2) not null,
  FLTNO     VARCHAR2(20) not null,
  FLTDATE   DATE not null,
  IO        CHAR(1) not null,
  APT       VARCHAR2(64) not null
);
insert into T_TEST Values ('CZ','123',To_date('2008-12-31','yyyy-MM-dd'),'I',',OAK,ANC,NRT,PEK,PVG,');
insert into T_TEST Values ('CZ','123',To_date('2008-12-31','yyyy-MM-dd'),'O',',AAA,BBB,CCC,DDD,');
commit;
select * from T_TEST;

Create or Replace view V_test
as
select T1.CarrierID,t1.FltNO,t1.FltDate,t1.IO,t1.SAPT,t2.EAPT from
(
       SELECT Rownum as rnum,TT.*,SUBSTR(APT,INSTR(APT,',',1,RN) + 1,INSTR(APT,',',1,RN + 1) - INSTR(APT, ',',1,RN) - 1) SAPT
       FROM T_TEST TT,
           (
             SELECT ROWNUM RN FROM ALL_OBJECTS
             WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -LENGTH(REPLACE(TRIM(',' FROM APT),',','')))
             FROM T_TEST TT) + 1
            ) ZZ
) T1,
(           
       SELECT Rownum as rnum,TT.*,SUBSTR(APT,INSTR(APT,',',1,RN) + 1,INSTR(APT,',',1,RN + 1) - INSTR(APT, ',',1,RN) - 1) EAPT
       FROM T_TEST TT,
           (
             SELECT ROWNUM RN FROM ALL_OBJECTS
             WHERE ROWNUM <= (SELECT MAX(LENGTH(TRIM(',' FROM APT)) -LENGTH(REPLACE(TRIM(',' FROM APT),',','')))
             FROM T_TEST TT) + 1
            ) ZZ
) T2
where T1.CarrierID=T2.CarrierID and t1.FltNO=t2.FltNO and t1.FltDate=t2.FltDate and t1.IO=t2.IO
and t1.rnum<t2.rnum and not t1.SApt is null and not t2.EApt is null
order by t1.rnum;

select * from V_test;
--drop view V_test;