oracle层级查询CONNECT BY PRIOR的用法

准备测试数据

with department as (
	SELECT '61515' as id, '1001' as dep_no, '部门1' as dep_name, '1' as dep_level, '0' as parent_dep, '1' as dep_order from dual
	UNION
	SELECT '14562', '1002', '部门2', '1', '0', '2' from dual
	UNION
	SELECT '41364', '1003', '部门3', '1', '0', '3' from dual
	UNION
	SELECT '74156', '100101', '部门1-1', '2', '1001', '1' from dual
	UNION
	SELECT '21456', '100102', '部门1-2', '2','1001', '2' from dual
	UNION
	SELECT '62149', '100103', '部门1-3', '2','1001', '3' from dual
	UNION
	SELECT '31546', '100201', '部门2-1', '2','1002', '1' from dual
	UNION
	SELECT '14635', '10010101', '部门1-1-1', '3','100101', '1' from dual
)

上面的临时表中简单存储了部门的信息，其中每个部门都存储了一个父级部门编号，dep_level是当前部门所处的层级，例如"部门1"处于1级部门，它的父级部门编号是0，也就是没有父级部门

sql层级查询

SELECT
	*
FROM
department
CONNECT BY PRIOR dep_no = parent_dep

以上sql代码可将部门关系分层级查询出来，结果如下

可以看到，这条语句将部门以及部门下的子部门按顺序显示了出来，但是如何理解CONNECT BY PRIOR dep_no = parent_dep呢

对CONNECT BY PRIOR的理解

CONNECT BY PRIOR dep_no = parent_dep可理解为将每一条数据，也就是每一个部门作为根节点，并且根据dep_no = parent_dep的条件向下生长，图示如下：

这是其中一个跟节点——部门1的层级情况，所有部门查询的结果并列排序，具体排列顺序取决于排序条件，没有排序条件以数据库的自然顺序排序。

指定根节点

SELECT
	*
FROM
department
CONNECT BY PRIOR dep_no = parent_dep
start with dep_no = '1001'

start with指定根节点，若不指定，所有数据均为根节点。

如果将sql改为如下写法，将会得到一个逆向生长的层级树

SELECT
	*
FROM
department
CONNECT BY dep_no = PRIOR parent_dep
-- 或者 CONNECT BY PRIOR parent_dep = dep_no
start with dep_no = '10010101'

排序方式

上面查询所有数据的结果显然不满足每一级部门顺序排序

我们想得到一级部门顺序排序，并且二级部门在一级部门下顺序排序，以此类推...

如果我们直接加上order by会打乱原本的层级结构

应该使用SIBLINGS关键字

SELECT
	*
FROM
department
CONNECT BY PRIOR dep_no = parent_dep
start with parent_dep = '0' -- 指定一级部门为根节点
ORDER SIBLINGS BY dep_order;

至此，得到了最终的顺序层级结构