---公交站点，从A站点开始，能够到达哪些站点?

create table bus(

    dstart varchar2(4),

    dend varchar2(4)

);

insert into bus values('A','B');

insert into bus values('B','C');

insert into bus values('A','D');

insert into bus values('D','M');

insert into bus values('C','E');

insert into bus values('F','M');

commit;

-----要求：将路线列示出来

select * from bus;

层次查询基本语法：

select [level], column, expr...from table [where condition] start with condition

connect by [prior column1= column2 | column1 = prior column2];

level

---可以查询出scott这个员工的上级领导

select level,

       empno,

       ename,

       mgr,

       CONNECT_BY_ROOT ename,

       SYS_CONNECT_BY_PATH(ename, '/')

  from emp

 start with ename is not null

connect by prior empno = mgr;

-----SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！它一定要和connect by子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

-----START WITH 代表你要开始遍历的的节点！

----CONNECT BY PRIOR 是标示父子关系的对应！CONNECT_BY_ROOT通过这个操作，可以获取树形查询根记录的字段 ,实现树状查询 左边的为父接点，右边为自接点

----CONNECT_BY_ROOT是指以ename为根向后进行遍历，所遍历到的上一层

SELECT level,ename "Employee", CONNECT_BY_ROOT ename "Manager",CONNECT_BY_ISLEAF isleaf,

   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path"

   FROM emp

   start with ename IS NOT NULL   -------可以查询哪一位员工下属员工具体有哪些人

   CONNECT BY PRIOR  empno = mgr

   ORDER BY "Employee",level, "Manager", "Pathlen", "Path";

通过以上例子就很容易得到公交路线查询思路如下：

select dstart,dend,row_number()over(partition by dstart order by null) rn1,row_number()over(partition by dend order by null) rn2 FROM BUS;

---方法一：

select dstart, wmsys.wm_concat(dend)  from bus group by dstart---可以列示出所有已起点可以到达的终点站

---方法二：

select dstart,dend, CONNECT_BY_ISLEAF isleaf,

sys_connect_by_path(dstart, '->')||'-'||dend "Path"

FROM bus

START WITH dstart IS NOT NULL

CONNECT BY PRIOR  dend =dstart;