DB2 uses SQL to implement recursive query (query the tree of the Organization based on the sub-organization)

CreatetableMAIN_NODE (primary, MLA_ROOTIDINTEGER, MLA_PARENTIDINTEGER, primary (50), PRIMARYKEY (MLA_ID) query the tree structure of the sub-institution; WHEREPARENT. Primary (parent institution and all its sub-organizations) WITHRPL

Create table MAIN_NODE (MLA_ID INTEGER not null, MLA_ROOTID INTEGER, MLA_PARENTID INTEGER, MLA_NAME VARCHAR (50), primary key (MLA_ID) // query the tree structure of the sub-organization; // where parent. mla_id = CHILD. mla_parentid (parent institution and all its sub-organizations) with rpl (m

Create table MAIN_NODE (
MLA_ID INTEGER not null,
MLA_ROOTID INTEGER,
MLA_PARENTID INTEGER,
MLA_NAME VARCHAR (50 ),
Primary key (MLA_ID)

)

// Query the tree structure of the sub-institution. // where parent. mla_id = CHILD. mla_parentid (PARENT institution and all its sub-organizations)

With rpl (mla_parentid, mla_id, mla_name)
(
Select root. mla_parentid, ROOT. mla_id, ROOT. mla_name FROM main_node root where root. mla_id = 3
UNION ALL
Select child. mla_parentid, CHILD. mla_id, CHILD. mla_name from rpl parent, main_node child where parent. mla_parentid = CHILD. mla_id
)
Select distinct mla_parentid, mla_id, mla_name from rpl order by mla_parentid, mla_id, mla_name

Let's look at this query statement:

• RPL is a virtual table with the following three columns: mla_parentid, mla_id, and mla_name.
• The first SELECT statement in the WITH clause is the initialization table. It is executed only once. The results form the initial content of the virtual table as the seeds of recursion. In the above example, the seed is one or more rows of mla_id as any parameter passed in.
• The second SELECT statement is executed multiple times. Transmit the seed as the input (secondary table in JOIN) to the second SELECT statement to generate the next row set. Add the JOIN result (union all) to the current content of the virtual table and put it back to it to form the input for the next transfer. This process will continue as long as there are rows generated.
• If expected, the final SELECT statement in the virtual table allows us to SELECT all or only some rows produced by recursive queries.