Oracle如何實現層次化查詢

小編給大家分享一下Oracle如何實現層次化查詢，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

Oracle層次化查詢

(1) 樹狀模型

假如有一個市場模型，其中一級市場是中國，二級市場是省份，三級市場是各個省下面的市，四級市場是各個市下面的縣。那么可以創建表market：

create table market(market_id number(2), market_name varchar2(10), parent_market_id number(2));

insert all

into market values(1, 'China', 0)

into market values(2, 'Shan Xi', 1)

into market values(3, 'Shan Dong', 1)

into market values(4, 'Fu Jian', 1)

into market values(5, 'Tai Yuan', 2)

into market values(6, 'Yang Quan', 2)

into market values(7, 'Qi Zhou', 2)

into market values(8, 'Qing Dao', 3)

into market values(9, 'Ji Nan', 3)

into market values(10, 'Yan Tai', 3)

into market values(11, 'Xia Men', 4)

into market values(12, 'Fu Zhou', 4)

into market values(13, 'Quan Zhou', 4)

into market values(14, 'Gu Jiao', 5)

into market values(15, 'Yang Qu', 5)

into market values(16, 'Qing Xu', 5)

select * from dual;

(2) 層次化查詢

select market_id, market_name

from market

start with market_name = 'Shan Xi'

connect by prior market_id = parent_market_id;

MARKET_ID MARKET_NAME

---------- ------------------------------

2 Shan Xi

5 Tai Yuan

14 Gu Jiao

15 Yang Qu

16 Qing Xu

6 Yang Quan

7 Qi Zhou

7 rows selected.

這里的搜索規則為：從'Shan Xi'節點開始搜索、前一個節點的market_id等于下一個節點的parent_markket_id。

(3) 相關函數

顯示搜索路徑的函數為

sys_connect_by_path（列名，分隔符）

示例：

select market_id, market_name, sys_connect_by_path(market_name, '/') as market_path

from market

start with market_name = 'Gu Jiao'

connect by prior parent_market_id = market_id;

MARKET_ID MARKET_NAM MARKET_PATH

---------- ---------- ----------------------------------------

14 Gu Jiao    /Gu Jiao

5 Tai Yuan   /Gu Jiao/Tai Yuan

2 Shan Xi    /Gu Jiao/Tai Yuan/Shan Xi

1 China      /Gu Jiao/Tai Yuan/Shan Xi/China

這里可以利用max()函數來獲得完整的路徑：

select max(sys_connect_by_path(market_name, '/')) market_path

from market

start with market_name = 'Gu Jiao'

connect by prior parent_market_id = market_id;

MARKET_PATH

----------------------------------------

以上是“Oracle如何實現層次化查詢”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！