语法

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这一关联后,就在查询结果集里再进行一次筛选。