--This article can be pasted directly into the Plsql run, the test table to create it yourself--Test name: Test Oracle Recursive--oracle version: oracle8i--Sample table: S_coalarea_test (Region table)--Table structure: country level (grade) 1 Level determines the hierarchy recorded in the tree--province level (rank) 2--area level (rank) 3--and so on--note: This area table is a tree, recursive query is mainly to facilitate the maintenance of the tree
--Test 1: Simple recursion--demand: Query all its sub-region names by region name-Note: Aid is table primary key, ParentID is parent node primary key, AreaName region name-Analysis: Recursive query is actually a condition filter select S.areaname From S_coalarea_test s start with--start with is a recursive entry, that is, the starting position of the recursion s.areaname = ' Heilongjiang '--Specify the entry conditions connect by Prior--prior pre-sequence traversal indicates the traversal direction into the default non-recursive query S.aid=s.parentid--cascading conditions are also the order in which the tree is connected by which field determines the query results should be noted
--Test 2: Talk about recursive entry start with-requirements: Based on multiple region name Finder all sub-regions select S.areaname from S_coalarea_test s start with S.areaname = ' Heilongjiang '--recursive entry can be multiple values, its principle: first according to start with filter record or--and then according to the filtered records, recursively traverse the result s.areaname= ' Shanxi ' connect by prior S.aid=s.parentid
--Test 3: Re-talk about recursive entry-requirements: Based on multiple region name Finder all sub-areas-error: The test results are annoying, the original start with where to execute, so this test is a failure, we should pay attention to the select S.areaname from S_ Coalarea_test s where s.areaname= ' heilongjiang ' or s.areaname = ' Shanxi ' start with s.areaname = S.areaname-Since start WI Th is just filtering records, then of course we can not filter here, and directly with where filter results of connect by prior S.aid=s.parentid
--Test 4: Talk about cascading conditions--requirements: According to the region name, query superior region Select S.areaname from S_coalarea_test s start with s.areaname= ' Datong ' Connect b Y Prior S.parentid=s.aid--note that at this point only the Cascade condition order has changed--summary: Whether it is a parent or child node, Cascade Order decision-rule: "This record field" = "Connection field" -If this instance: is the aid (primary key) that matches other records through the parentid of this record, the result is a parent node--if the order is reversed, the aid (primary key) with this record matches the parentid of the other records, and the result is a child node --Test 5: Where the recursive keyword appears-demand: Find its province by region name
Select S.areaname from S_coalarea_test s where s.arealevel=1-filters the traversal results to start with the--start with must appear in the where, but can is between where and into the following example s.areaname= ' Datong ' Connect by prior S.parentid=s.aid
--Test 5: Continue talking about where the recursive keyword appears-demand: Find its province by region name
Select S.areaname from S_coalarea_test s where s.arealevel=1--filters the traversal results to start with--start with no need to add and S.areaname= ' Datong ' connect by prior S.parentid=s.aid and s.arealevel=1---where filter conditions
GROUP BY S.areaname-since recursive queries are also filters, of course, you can follow GROUP by
ORDER BY S.areaname--since recursive queries are also filtering conditions, of course, you can follow order by
--Summary: Start with ... the Connect by prior recursive query also filters the results, similar to where filtering, prior to where filtering executes http://my.oschina.net/lovedreamland/blog/90284
Oracle recursive query
