資料庫開發基礎---層次查詢

 

---公交站點，從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;