create table MAIN_NODE ( MLA_ID INTEGER not null, MLA_ROOTID INTEGER, MLA_PARENTID INTEGER, MLA_NAME VARCHAR(50), PRIMARY KEY (MLA_ID) ) //查询子机构所属 树结构； //WHERE PARENT. mla_id= CHILD.mla_parentid (父机构及其所有子机构) WITH RPL (m
create table MAIN_NODE ()
//查询子机构所属 树结构； // WHERE PARENT. mla_id= CHILD.mla_parentid (父机构及其所有子机构)
WITH RPL (mla_parentid, mla_id, mla_name) AS
(
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
让我们研究这个查询语句：
