Preface: Develop the commonly used relational database mysql,mssql,postgre,oracle, simple to delete and change the SQL statements are compatible with standard SQL, this does not speak, then for iterative query (not strict term: recursive query) Each kind of database is different, The best support for standard SQL statements is MSSQL and POSTGRE, which does not need to be talked about, we only talk about how Postgre in a single-table case can get hierarchical data through iterative queries.
Example of a table structure
Menu table
ID VARCHAR2 (+) N Sys_guid () Node ID
FENXIDVARCHAR2 (+) Y sub-item ID
FENXMCVARCHAR2 (+) Y sub-item name
FUJIDVARCHAR2 (+) Y Parent ID
FUJMCVARCHAR2 () Y Parent name
Ii. related relations
The Fujid of the child menu equals the Fenxid of the parent menu, the root node has no Fujid, and each node has a node ID to facilitate a single node additions and deletions
That is ((root node, root node no Fujid) Id,fenxid-->fujid,fenxid (parent node with child, the FUJD equals the fenxid of the previous level)-->fujid,fenxid-->fujid,fenxid-- >fujid,fenxid .... And so on
Third, iterative query (1) Unlimited level query
With RECURSIVE locs (ID,FENXID,FENXMC,FUJID,FUJMC)
As
(
SELECT a.id,a.fenxid,a.fenxmc,a.fujid,a.fujmc,0 as loclevel from menu a
where a.id= ' 12345678000000000000 '
UNION All
SELECT a.id,a.fenxid,a.fenxmc,a.fujid,a.fujmc,loclevel+1 from Menu A
INNER JOIN locs p on A.fujid=p.fenxid
)
SELECT * from Locs
Each data for the query results is taken with a loclevel field that represents the hierarchy
(2) Limit number of levels query
For example, we only need to query to the third Level menu and add a loclevel<3 condition to the SQL statement above:
With RECURSIVE locs (ID,FENXID,FENXMC,FUJID,FUJMC)
As
(
SELECT a.id,a.fenxid,a.fenxmc,a.fujid,a.fujmc,0 as loclevel from menu a
where a.id= ' 12345678000000000000 '
UNION All
SELECT a.id,a.fenxid,a.fenxmc,a.fujid,a.fujmc,loclevel+1 from Menu A
INNER JOIN locs p on A.fujid=p.fenxid and loclevel<3
)
SELECT * from Locs
Use the Postgre database to implement a child-parent iterative query of a tree-structured table, with a simple example through a cascading menu