Use the Postgre database to implement a child-parent iterative query of a tree-structured table, with a simple example through a cascading menu

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