Oracle 中的树查询和 connect by

• Oracle 中的树查询和 connect by
  
  使用 connect by 和 start with 来建立类似于树的报表并不难，只要遵循以下基本原则即可：
  
  使用 connect by 时各子句的顺序应为：
  select
  from
  where
  start with
  connect by
  order by
  
• prior 使报表的顺序为从根到叶（如果 prior 列是父辈）或从叶到根（如果 prior 列是后代）。
  
• where 子句可以从树中排除个体，但不排除它们的子孙（或者祖先，如果 prior 列是后代）。
  
• connect by 中的条件（尤其是不等于）消除个体和它所有的子孙（或祖先，依赖于怎样跟踪树）。
  
• connect by 不能与 where 子句中的表连接在一起使用。

下面是几个例子

1. 从根到叶遍历
SELECT n_parendid, n_name, (LEVEL - 1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;

2. 从叶到根遍历
SELECT n_parendid, n_name, (LEVEL - 1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 300
CONNECT BY n_id = PRIOR n_parendid;

3. 排除个体，但不排除它们的子孙
SELECT n_parendid, n_name, (LEVEL - 1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL AND n_id != 2
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;

4. 消除个体和它所有的子孙
SELECT n_parendid, n_name, (LEVEL - 1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id AND n_id != 2;

5. 改变显示顺序
SELECT n_parendid, n_name, (LEVEL - 1), n_id
FROM navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id
ORDER BY n_viewnum DESC;