oracle常用语法

oracle常用语法

​ 在数据库中，大家经常会用到递归查询数据，比如根据根节点分类，需要知道根节点，或者过滤时父级节点不断，需要形成完整树时，会用到start with

表结构如下

1、查询id为2以下的各级节点(包括各级父节点)

SELECT
	*
FROM
	"CS_a_tree_pid1"
START WITH
	"id" = '2'
CONNECT BY
	"pid" = PRIOR "id"

2、查询id为2下所有后代节点（包含各级父节点）

SELECT
	*
FROM	
	"CS_a_tree_pid1"
START WITH
	"pid" = '2'
CONNECT BY
	"pid" = PRIOR "id"

3、查询id为6所有父节点（所有祖宗节点包括本身）

SELECT
	*
FROM
	"CS_a_tree_pid1"
START WITH
	"id" = '6'
CONNECT BY
	PRIOR "pid" = "id"

4、查询id为2的所有亲兄弟

SELECT
	*
FROM
	"CS_a_tree_pid1" c
WHERE
	EXISTS (
	SELECT
		*
	FROM
		"CS_a_tree_pid1" s
	WHERE
		c."pid" = s."pid"
		AND "id" = '2')

e_pid1" s
WHERE
c.“pid” = s.“pid”
AND “id” = ‘2’)

level字段可以用来查询层级数，也可以用with模拟带有层级的临时表