Oracle recursive query

1. Grammatical structure:

Select... start with...connect by...prior

For example: there is a unit (unit) Table with three field IDs, Name,parentid (unit ID, Unit name, ancestor's unit ID)

1.1 Query all units below the unit

Select  from unit t start with t.id=' units ID' connect by prior  t.id= T.parentid

1.2 Query all units above the unit

Select  from unit t start with t.id=' units ID' connect by  t.id= Prior T.parentid

2. Remark:

Start with logo from where to start checking the  prior  logo up or down.

3. Expand:

Recursive queries tend to be sorted with siblings, which is what we'll use: ORDER siblings by

For example: querying all units and sorting the units of the same level by phonetic Alphabet

Select  from unit t start with t.id=' units ID' connect by prior  t.id= T.parentid ORDER sibli NGS by Nlssort (T.name,'nls_sort=schinese_pinyin_m')

Oracle recursive query