oracle 树

做了个实验总结一下connect by树查询的用法：

SQL> select * from t_dept_temp;

   DEPT_ID  PARENT_ID DEPT_NAME      AMOUNT
---------- ---------- ---------- ----------
         1            1                   2
         2          1 1-2                15
         3          1 1-3                 8
         4          2 1-2-4              10
         5          2 1-2-5               9
         6          3 1-3-6              17
         7          3 1-3-7               5
         8          3 1-3-8               6

已选择8行。

SQL> desc t_dept_temp;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPT_ID                                   NOT NULL NUMBER(2)
PARENT_ID                                          NUMBER(2)
DEPT_NAME                                          VARCHAR2(10)
AMOUNT                                             NUMBER(3)

接下来实现各种查询的测试：
1.查询dept_id=2以及所有下级部门：
SQL> select level treelevel,dept_id,parent_id,dept_name from      t_dept_temp
  2  start with dept_id=2
  3  connect by parent_id = prior dept_id;

TREELEVEL    DEPT_ID  PARENT_ID DEPT_NAME
---------- ---------- ---------- ----------
         1          2          1 1-2
         2          4          2 1-2-4
         2          5          2 1-2-5

2.查询dept_id=4以及所有上级部门：
SQL> select level treelevel,dept_id,parent_id,dept_name from t_dept_temp
  2  start with dept_id=4
  3  connect by dept_id = prior parent_id;

TREELEVEL    DEPT_ID  PARENT_ID DEPT_NAME
---------- ---------- ---------- ----------
         1          4          2 1-2-4
         2          2          1 1-2
         3          1            1

3.查询dept_id=1以及所有下级部门：
SQL> select level treelevel,dept_id,parent_id,dept_name from t_dept_temp
  2  start with dept_id=1
  3  connect by parent_id = prior dept_id;

TREELEVEL    DEPT_ID  PARENT_ID DEPT_NAME
---------- ---------- ---------- ----------
         1          1            1
         2          2          1 1-2
         3          4          2 1-2-4
         3          5          2 1-2-5
         2          3          1 1-3
         3          6          3 1-3-6
         3          7          3 1-3-7
         3          8          3 1-3-8

已选择8行。

4.查询dept_id=1以及所有下级部门，但除了部门3 (排除树枝，部门3下的所有节点也都排除)
SQL> select level treelevel,dept_id,parent_id,dept_name from t_dept_temp
  2  start with dept_id=1
  3  connect by parent_id=prior dept_id and dept_id <> 3;

TREELEVEL    DEPT_ID  PARENT_ID DEPT_NAME
---------- ---------- ---------- ----------
         1          1            1
         2          2          1 1-2
         3          4          2 1-2-4
         3          5          2 1-2-5

5.查询dept_id=1以及所有下级部门，但除了部门3 (仅排除部门3,其下的所有节点都是包括的)
SQL> select level treeview, dept_id,parent_id,dept_name from t_dept_temp
  2  where dept_id <> 3
  3  start with dept_id=1
  4  connect by parent_id = prior dept_id;

  TREEVIEW    DEPT_ID  PARENT_ID DEPT_NAME
---------- ---------- ---------- ----------
         1          1            1
         2          2          1 1-2
         3          4          2 1-2-4
         3          5          2 1-2-5
         3          6          3 1-3-6
         3          7          3 1-3-7
         3          8          3 1-3-8

已选择7行。
注意这里的where dept_id <> 3,他的执行是在connect by之后做的