Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.