我們在日常程式設計中,經常會遇到樹狀結構的表示,例如組織機構、行政區劃等等。這些在資料庫中往往通過一張表進行展示。這裡我們以一張簡單的行政區劃表為例進行展示,在實際使用過程中,可以為其添加其他描述欄位以及層級。表中通過ID和PID關聯,實現樹狀結構的存儲。建表以及數據語句如下:-- Create t ...
我們在日常程式設計中,經常會遇到樹狀結構的表示,例如組織機構、行政區劃等等。這些在資料庫中往往通過一張表進行展示。這裡我們以一張簡單的行政區劃表為例進行展示,在實際使用過程中,可以為其添加其他描述欄位以及層級。
表中通過ID和PID關聯,實現樹狀結構的存儲。建表以及數據語句如下:
-- Create table create table TREETEST ( id NVARCHAR2(50), pid NVARCHAR2(50), name NVARCHAR2(50) )
insert into TREETEST (ID, PID, NAME) values ('1', null, '山東省'); insert into TREETEST (ID, PID, NAME) values ('2', '1', '青島市'); insert into TREETEST (ID, PID, NAME) values ('3', '1', '煙臺市'); insert into TREETEST (ID, PID, NAME) values ('4', null, '河南省'); insert into TREETEST (ID, PID, NAME) values ('5', null, '河北省'); insert into TREETEST (ID, PID, NAME) values ('6', '2', '市南區'); insert into TREETEST (ID, PID, NAME) values ('7', '2', '市北區'); insert into TREETEST (ID, PID, NAME) values ('8', '2', '即墨市');
那對於樹狀結構如何查詢呢?Oracle提供遞歸查詢的方式進行查詢,基本語法如下:
SELECT [Column]…..
FEOM [Table]
WHERE Conditional1
START WITH Conditional2
CONNECT BY PRIOR Conditional3
ORDER BY [Column]
說明:條件1---過濾條件,對全部返回的記錄進行過濾。
條件2---根節點的限定條件,固然也可以放寬許可權得到多個根節點,也就是獲取多個樹
條件3---鏈接條件,目的就是給出父子之間的關係是什麼,根據這個關係進行遞歸查詢(在上述表中就是ID=PID)
排序---對全部返回記錄進行排序
下麵我們結合具體實例來看:
1、查詢山東省下麵的所有子節點
SELECT * FROM TREETEST t START WITH t.PID=1 CONNECT BY PRIOR t.ID = t.PID
其中ID為1的為山東省節點,查詢結果如下
2、查詢青島市的下一級子節點(註意和上面區分,全部子節點和下一級子節點)
3、如果需要獲取將山東省青島市等連接起來顯示,可以使用SYS_CONNECT_BY_PATH來實現
SELECT t.ID, SYS_CONNECT_BY_PATH(t.NAME, '\') AS INDU_NAME FROM TREETEST t START WITH t.PID IS NULL CONNECT BY PRIOR t.ID = t.PID
查詢結果如下:
4、同理,也可以從下往上進行查詢
SELECT * FROM TREETEST t START WITH t.ID=8 CONNECT BY t.ID = PRIOR t.PID