How to implement hierarchical query by Oracle
Editor to share with you how to achieve hierarchical query Oracle, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!
Oracle hierarchical query
(1) Tree model
If there is a market model, in which the primary market is China, the secondary market is the province, the tertiary market is the city under each province, and the fourth market is the county below each city. Then you can create the table 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) hierarchical query
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.
The search rule here is that the search starts with the 'Shan Xi' node, and the market_id of the previous node equals the parent_markket_id of the next node.
(3) correlation function
The function that displays the search path is
Sys_connect_by_path (column name, delimiter)
Example:
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
Here you can use the max () function to get the complete path:
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
-
The above is all the content of the article "how to achieve hierarchical query in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
