B-tree结构菜单的递归查询

有层次结构的菜单树，后台库表结构通常会设计为B-tree结构，

就是菜单节点上有一个PID字段，记录自己的父节点号，

通过这种自关联的方式隐含树的层次结构，如：

CREATE TABLE T_MENU(
ID    NUMBER,  -- 节点号
-- NAME  VARCHAR2(50),
PID   NUMBER DEFAULT 0  -- 父节点号。0为根节点,不存在id=0的节点
);

INSERT INTO T_MENU (ID, PID) VALUES ('1', '0');
INSERT INTO T_MENU (ID, PID) VALUES ('2', '1');
INSERT INTO T_MENU (ID, PID) VALUES ('3', '1');
INSERT INTO T_MENU (ID, PID) VALUES ('4', '2');
INSERT INTO T_MENU (ID, PID) VALUES ('5', '2');
INSERT INTO T_MENU (ID, PID) VALUES ('6', '3');
INSERT INTO T_MENU (ID, PID) VALUES ('7', '3');
INSERT INTO T_MENU (ID, PID) VALUES ('8', '5');
INSERT INTO T_MENU (ID, PID) VALUES ('9', '5');
INSERT INTO T_MENU (ID, PID) VALUES ('10', '7');
INSERT INTO T_MENU (ID, PID) VALUES ('11', '7');
INSERT INTO T_MENU (ID, PID) VALUES ('12', '10');
INSERT INTO T_MENU (ID, PID) VALUES ('13', '10');

SELECT * FROM T_MENU T ORDER BY T.ID ASC;

上面初始化的数据，其树形结构如下：

 

这种结构的菜单数据，该如何查询呢，通常来说，我们会有两种查询场景:

• 给出父节点号，查询它下边的所有子孙节点；

• 给出子节点号，查询它在菜单树中的位置。

这两个查询，可通过SQL的 start with ... connect by prior 递归查询法查询

 

向下遍历，给出父节点ID=7, 查询它的所有子节点

SELECT T.ID, T.PID, LEVEL
  FROM T_MENU T
 START WITH T.PID = '7'
CONNECT BY PRIOR T.ID = T.PID
 ORDER BY LEVEL ASC;

查询结果如下:

ID	PID	LEVEL
10	7	1
11	7	1
12	10	2
13	10	2

 

 

 

 

 

 

向上遍历，给出节点ID=7, 查询它的所有父节点序列

SELECT T.ID, T.PID, LEVEL
  FROM T_MENU T
 START WITH T.ID = '7'
CONNECT BY T.ID = PRIOR T.PID
 ORDER BY LEVEL DESC;

查询结果如下:

ID	PID	LEVEL
1	0	3
3	1	2
7	3	1

 

 

 

 

SQL解释

START WITH 子句: 这是遍历的起始条件，也是遍历结果中第一层(LEVEL = 1)的数据条件。CONNECT BY PRIOR 子句: 这是表自关联的连接条件，PRIOR字段是已有数据行的字段，用它与表中其它行的非PRIOR字段进行关联。CONNECT BY 后边可以有多个条件，每个条件都可以有或没有PRIOR关键字.

自关联递归是如何进行的呢？根据START WITH 条件找到起始记录后，在起始记录中取PRIOR字段的值，然后在本表中查询这样的数据行：它的非PRIOR字段值与起始记录的PRIOR字段值 之间的关系，符合CONNECT BY 条件.

以上面例子中的向下遍历为例，具体地，先根据起始条件找到PID = '7'的记录，有两条，分别是ID = 10 和 ID = 11，这是查询的起始记录，也就是第一层.然后连接条件是

PRIOR T.ID = T.PID 

那是说要查找PID = 起始记录的ID值(即10和11) 的数据，没有PID = 11的记录，PID = 10的记录有两条，即ID 为12 和13 的记录，这就是查询结果的第二层，以此类推，以第二层记录为起始记录，查找PID=12或13的记录，为第三层，这里没有第三层数据，递归结束.

相关写法

在Oracle中有一种与此等效的写法，就是使用WITH AS 语句递归查询，这种写法显式地进行了递归自关联查询，其查询过程与上面语法十分相似，可以通过分析这种写法来理解上面的语法含义.

下面把上述例子中的向上遍历SQL，改写为WITH AS 递归写法:

WITH TMP(ID, PID) AS
 (SELECT T.ID, T.PID
    FROM T_MENU T
   WHERE T.ID = '7'
  UNION ALL
  SELECT B.ID, B.PID
    FROM TMP A, T_MENU B
   WHERE A.PID = B.ID)
SELECT M.ID, M.PID, ROWNUM FROM TMP M ORDER BY ROWNUM DESC;