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模拟带有层级的临时表