Oracle的递归查询

语法

SELECT * from table_name start with 条件2 connect by 条件3 [where 条件1];

条件1是对结果集的再筛选。

条件2确定起始节点，可以确定多个起始节点，可以理解成查询出来的结果树的叶子节点或者根节点。

条件3是连接条件，通过关键字prior指定上一条记录。

实例

我们称表中的数据存在父子关系，通过列与列来关联的，这样的数据结构为树结构。
现在有一个menu表，字段有id，pid，title三个。

• 查询菜单id为10的所有子菜单。

SELECT * FROM tb_menu m START WITH m.id=10 CONNECT BY m.pid=PRIOR m.id;

将PRIOR关键字放在m.id前面，意思就是查询pid是当前记录id的记录，如此顺延找到所有子节点。

• 查询菜单id为40的所有父菜单。

SELECT * FROM tb_menu m START WITH m.id=40 CONNECT BY PRIOR m.pid= m.id ORDER BY ID;

LEVEL关键字

• 查询一个节点的父节点的的兄弟节点（伯父与叔父）
  因为创建的表没有LEVEL的字段，可以

WITH TMP AS
 (SELECT M.*, LEVEL LEV
    FROM MENU M
   START WITH M.PID = 0
  CONNECT BY PRIOR M.ID = M.PID)
SELECT * FROM TMP;

tmp的效果就是上面这样，LEV就是菜单所在的深度或者层级。

WITH TMP AS
 (SELECT M.*, LEVEL LEV
    FROM MENU M
   START WITH M.PID = 0
  CONNECT BY PRIOR M.ID = M.PID)
SELECT *
  FROM TMP
 WHERE PID =
       (SELECT PID FROM TMP WHERE ID = (SELECT PID FROM TMP WHERE ID = 21));

注意查询的不是id=20的父亲的同等级的菜单而是父亲的兄弟菜单，也就是跟父菜单具有相同pid的节点。

EXISTS和NOT EXISTS

SELECT COUNT(*)
  FROM TB_MENU M1
 WHERE EXISTS (SELECT *
          FROM TB_MENU M2
         WHERE ID = -1
           AND M1.ID = M2.ID)
   AND M1.ID = 2;

以这个查询语句为例，如果EXISTS或者NOT EXISTS后面的查询条件没有 m1.id=m2.id，其实后面的查询结果就相当于true或者false这两个值，为true，无需考虑；为false，结果为0；
加上m1.id=m2.id这一关联后，就在查询结果集里再进行一次筛选。