connect by 结构化查询 oracle_11g

sql oracle mysql 数据库 基础 知识 窗口函数 lag与lead函数 LISTAGG函数 connect by 结构化查询 备份和恢复 mysql5.7 mysql8.0 oracle_11g docker

connect by 结构化查询 oracle_11g

用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询

语法格式:
　　　　{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
　　　　| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
　　　　}

特殊词讲解：

　　　　start with: 是根结点的限定语句，可以放宽限定条件，以取得多个根结点，实际就是多棵树

　　　　connect by: 是连接条件，其中用prior表示上一条记录[指定父子行的条件关系]

　　　　prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ...

​ 例：connect by prior id=pid就是说上一条记录的id是本条记录的pid，即本记录的父亲是上一条记录。　　　　

​ nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条

　　　　循环行: 该行只有一个子行，而且子行又是该行的祖先行

　　　　connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是

　　　　connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是

　　　　level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点

常用生成数字序列结果集

  -- 生成数字序列结果集
  select rownum from dual
  connect by rownum <= 3;

-- 创建表
create table test3(
	 id number(18),
	 pid number(18),
	 name varchar2(200),
	 salary number(10,2),
	 dept_no varchar2(8)
);

-- 添加数据
insert into test3 values('1',0,'序列1','1000000.00','001');commit;
insert into test3 values('2',1,'序列2','50500.00','002');commit;
insert into test3 values('3',1,'序列3','60000.00','003');commit;
insert into test3 values('4',2,'序列4','30000.00','002');commit;
insert into test3 values('5',2,'序列5','25000.00','002');commit;
insert into test3 values('6',3,'序列6','23000.00','003');commit;
insert into test3 values('7',3,'序列7','21000.00','003');commit;

查询以pid为0开始的节点的所有直属节点

SELECT
	id 当前ID,
	pid 父级ID,
	NAME 当前姓名,
	prior NAME AS 父级姓名, -- prior 代表为上一条记录的姓名
	salary 工资
FROM
	test3 START WITH pid = 0 
	connect BY prior id = pid -- prior 上一条记录ID = 当前行PID

-- 等同于

SELECT
	id "当前ID",
	pid "父级ID",
	NAME "当前姓名",
	prior NAME AS "父级姓名", -- prior 代表为上一条记录的姓名
	salary "工资"
FROM
	test3 START WITH id = 1 
	connect BY prior id = pid

以emp_id为6的所有祖先节点

SELECT
	id 当前ID,
	pid 父级ID,
	NAME 当前姓名,
	salary 工资
FROM
	test3 START WITH id = 6  -- 是根结点的限定语句拿到第一条记录
	connect BY prior pid = id;

查询一个节点的叔叔伯父节点

查看id为6的节点的叔叔伯父节点

WITH temp AS (  -- 查出所有树结构
	SELECT
		id,
		pid,
		NAME,
		prior NAME AS pname,-- prior 代表为上一条记录的姓名
		LEVEL le 
	FROM
		test3 START WITH pid = 0 
		connect BY pid = prior id 
)

SELECT
		selfT.id 当前ID,
		selfT.pid 父级ID,
		selfT.name 当前姓名,
		selfT.pname 父级姓名,
		selfT.le 当前层级,
		parentT.id 叔伯ID,
		parentT.pid 叔伯父级ID,
		parentT.name 叔伯姓名,
		parentT.pname 叔伯父级姓名,
		parentT.le 叔伯层级
FROM
	temp parentT
	LEFT JOIN temp selfT ON selfT.id = 6 -- 此处需要限定
	
WHERE
	parentT.le = ( selfT.le - 1 )  -- 根据自己层级获得上一层级
	AND parentT.id NOT IN ( selfT.pid ); -- 排查自己上级

查询族兄

查看test3 id是6的节点的族兄节点

WITH temp AS (  -- 查出所有树结构
	SELECT
		id,
		pid,
		NAME,
		prior NAME AS pname,-- prior 代表为上一条记录的姓名
		LEVEL le 
	FROM
		test3 START WITH pid = 0 
		connect BY pid = prior id 
)

SELECT
		brotherT.id 兄ID,
		brotherT.pid 兄父级ID,
		brotherT.name 兄姓名,
		brotherT.pname 兄父级姓名,
		brotherT.le 兄层级
FROM
	temp brotherT
	LEFT OUTER JOIN temp selfT ON selfT.id = 6 -- 此处需要条件限制
	
WHERE
	brotherT.le = selfT.le  -- 查询自己层级的其它族兄
	AND brotherT.id <> 6 -- 此处需要条件限制

level伪列的使用，格式化层级 level数值越低级别越高

lpad

lpad( string, padded_length, [ pad_string ] )
string 准备被填充的字符串
padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

SELECT
	lpad( ' ', LEVEL * 2, ' ' ) || NAME 当前姓名,
	id 当前ID,
	pid 父级ID,
	salary 工资,
	LEVEL 当前层级
FROM
	test3 START WITH pid = 0 
	connect BY prior id = pid;

connect_by_root 查找根节点

SELECT
	connect_by_root NAME 根结点,  -- 根据限定拿到序列2  序列3
	name 当前姓名,
	pid 父级ID,
	salary 工资
FROM
	test3 START WITH pid = 1   -- 限定父级ID=1  序列2  序列3
	connect BY prior id = pid;

注意： connect_by_root关键字后面跟着字段，表示根节点对应记录的某一字段的值，
如 connect_by_root name表示根节点的员工名，connect_by_root salary表示根节点的工资

标注循环行

-- 插入一条数据，与另一条id=7的数据组成循环行
INSERT INTO test3 VALUES ( '3', 7, '循环行', '21000.00', '003' );COMMIT;

-- connect_by_iscycle("CYCLE"), connect by nocycle
SELECT
	id 当前ID,
	name 当前姓名,
	pid 父级ID,
	salary 工资,
	connect_by_iscycle AS 循环关系 
FROM
	test3 START WITH pid = 0 
	connect BY nocycle prior id = pid;  -- nocycle 防止出现父子关系循环

最后一行与新追加的一行是循环关系，因此connect_by_iscycle列显示值为1，但结果集只显示循环的第一条，与之循环的另外一条(新追加的一条)是不显示的
connect by 后面的nocycle关键字是防止出现父子关系循环的，如果表中出现父子关系循环且没有使用该关键字，会报如下错误：
ORA-01436: CONNECT BY loop in user data 用户数据中的 CONNECT BY 循环

connect_by_isleaf 是否是叶子节点

SELECT
	id 当前ID,
	name 当前姓名,
	pid 父级ID,
	salary 工资,
	connect_by_isleaf 是否叶子节点,
	decode(connect_by_isleaf, 0, '否', 1, '是') 是否叶子节点中文
FROM
	test3 START WITH pid = 0 
	connect BY nocycle prior id = pid;

叶节点指的是没有子节点的节点，那些是既是父节点又是子节点的节点不属于叶节点