Oracle recursive query

In database queries, data in the tree structure is often queried. You need to use the data of a field as the parent node of the next record to continue the query, if you do not know how many nodes there are, writing SQL queries again and again will be cumbersome and inefficient, you can use

Connect with prior recursion in Oracle Algorithm :

Oracle Start with... connect by priorClause usage connect by is used in structured queries. Its basic syntax is:
Select
... From tablename Start with condition 1 --Specify the root node (Start Node) in the recursive query).
Connect
By condition 2
Where
Condition 3;
Example:
Select
* From table
Start
With org_id = 'hbh1_wgwpy'
Connect
By prior org_id = parent_id; In actual use, there are two problems: first, if you place the WHERE clause in start with... connect
By... after the clause will prompt: ORA-00933: SQL command is not completely complete. Place the cursor at the where position. Start with... connect
By... clause can be executed normally. Second, the queried data cannot have self-loops, that is, in the example Org_id And
Parent_id Cannot be equal in the same record, otherwise the prompt: ORA-01436: connect in user data
By loop. Pay attention to these two points.

In fact, after oracle9,Sort"Order siblingsTo meet our needs, the usage is as follows:

SelectD .*FromTest_tree d

StartWithD. PID = 0

ConnectByPriorD. id = D. PID

OrderSiblingsByD. IndASC