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;
 

展开阅读全文
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值