Tree query of common Oracle databases

The recursion data in the tree query table of common Oracle databases is as follows: id name parentid1 food classification-12 Meat 13 vegetables 14 product classification-15 health care products 46 Medicine 47 Building 4 a oracle implementation method: directly supported in Oracle, use the statement select * from tablename start with connect byprior id (child-layer column) = parentid (top-level column: 13061124399 start with specifies the conditions for starting a hierarchy. That is, the rows that meet this condition can be used as the association conditions between the top-level connect by prior layers of the hierarchy tree, that is, the query result of select * from recursionstart with connect by prior> is as follows: id name parentid1 food classification-12 Meat 13 vegetables Class 1 ii mssql Implementation Method in MSSQL requires the use of temporary tables and loop multiple queries. create function: create function GetRecursion (@ id int) returns @ t table (idint, namevarchar (50), parentidint) asbegin insert @ tselect * from recursion where> while @ rowcount> 0 insert @ t select. * from recursion as a inner join @ t as B on. parentid = B. id and. id not in (select id from @ t) returnend usage method: select * from GetRecursion (4) query result: id name parentid4 product category-15 health care products 46 Medicine 47 Building 4 3 MYSQL implementation method query statement: select B. id, B. name, B. parentid from recursion as a, recursion as bwherea. id = B. parentid and (. id = 1 or. parentid = 1) query results: id name parentid2 Meat 13 vegetables Class 1 4 in ORACLE, MSSQL, MYSQL, you can use the following query statement to return only the child node data of the tree structure table select * from tablename t where not exists (select' x' from tablename t1, tablename t2 where t1.id = t2.parentid and t1.id = t. id) for example: select * from recursion t where not exists (select 'x' from recursion t1, recursion t2 where t1.id = t2.parentid and t1.id = t. id) query result: id name parentid2 Meat 13 Vegetables 15 health care products 46 Medicine 47 Building 4 5 in ORACLE, MSSQL, MYSQL you can use the following query statement to return only the root node data of the tree table select * from tablename t where not exists (select 'x' from tablename t1, tablename t2 where t1.id = t2.parentid and t1.id = t. parentid) for example: select * from recursion t where not exists (select 'x' from recursion t1, recursion t2 where t1.id = t2.parentid and t1.id = t. parentid) Query Result: id name parentid1 food category-14 product category-1