oracle connect by 转postgresql with recursive 的初步认识

本文分享了从Oracle数据库迁移到PostgreSQL的具体方法,包括使用WITH RECURSIVE子句进行层级查询的技巧,适用于查询本级及上下级数据的需求。通过实际案例,展示了如何在PostgreSQL中实现Oracle的CONNECT BY功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

近期公司需要将oracle转成postgresql,现总结如下:

//code是本级编号,srd_code是上级编号

查询本级以及下级:
select * 
from student t
start with t.code = '16120624' 
connect by prior t.code = srd_code 

查询本级以及上级:
select *
from student t
start with t.code = '16120624'
connect by t.code = prior srd_code 

查询本级以及下级:
with recursive cte as(
    select x.* 
    from student x
    where x.code = '16120624'
    union all 
    select y.*
    from student y
    join cte c on c.code = y.srd_code and y.srd_code = '16120624'
)select code from cte

现在尚且还没有想出查询本级以及上级的代替方式,如果有欢迎各位补充教我一下,谢谢

考虑到没有数据没有参考性,现给出如下数据: 

 这里查出了习水县以及其下面所有部门

查阅了一下资料,连同本级向上查询的方法如下:

with recursive cte as(
    select x.* from student x where x.code = '1612624'
    union all
    select y.* from student y,cte where y.code = cte.srd_code 
)select * from cte


这里查出了连同习水县及其以上的单位 

不足之处望大神批评指正

<think>嗯,用户想了解OracleCONNECT BY的用法或者解决相关问题,特别是要一个示例教程。首先,我得回忆一下自己知道的关于CONNECT BY的知识点。CONNECT BYOracle中用于处理层次化查询的,比如树形结构或者层级关系的数据。常见的应用场景包括组织架构查询、菜单层级、分类树等。 根据用户提供的引用内容,里面有提到NOCYCLE参数和CONNECT BY的基本语法结构。引用[2]和[4]都提到基本语法是SELECT ... FROM tablename START BY cond1 CONNECT BY cond2 WHERE cond3。所以,我需要先解释基本语法结构,然后给出示例,可能还需要说明循环处理,比如NOCYCLECONNECT_BY_ISCYCLE的用法。 用户可能需要知道如何避免循环导致的错误,这时候NOCYCLE参数就派上用场了,配合CONNECT_BY_ISCYCLE伪列来检测循环。引用[1]和[3]都提到了这一点,所以这部分内容应该包含在回答里。 接下来,我需要组织回答的结构。首先介绍CONNECT BY的作用和基本语法,然后分点说明各个部分:START BYCONNECT BY、WHERE的区别,层级函数和伪列,循环处理。每个部分都需要有示例代码,比如创建一个示例表,插入数据,然后展示查询语句和结果。 示例部分可能需要一个员工表的例子,因为这是层次结构查询的典型场景。例如,每个员工有上级,通过CONNECT BY PRIOR来连接上下级关系。同时,要展示如何显示层级路径、层级深度等伪列的使用。 在循环处理的部分,需要模拟一个循环数据的情况,比如某个员工的上级指向自己或形成环,这时候使用NOCYCLE参数,并配合CONNECT_BY_ISCYCLE来检测哪里出现了循环。 最后,生成相关问题,可能涉及性能优化、应用场景、与其他递归查询的区别,比如和PostgreSQLWITH RECURSIVE对比,或者如何处理多层级查询中的过滤条件。 需要注意的是,用户提供的引用中有重复的内容,比如引用[1]和[3]都讲到了NOCYCLECONNECT_BY_ISCYCLE,引用[2]和[4]的语法部分相同。所以在引用标识时要注意正确的对应,避免重复引用。 另外,用户可能对层级查询中的条件过滤有疑问,比如WHERE和CONNECT BY中的条件有什么区别,这需要解释执行顺序的不同,WHERE是在层次结构构建后过滤,而CONNECT BY中的条件影响连接过程。 确保回答中使用正确的LaTeX格式,行内公式用$...$,独立公式用$$...$$,但在这个问题中可能涉及较少的数学公式,更多是SQL语法,所以要注意代码块的正确标记,使用反引号包裹代码示例。 最后,检查是否所有引用都正确对应,比如引用[^1]、[^2]、[^3]、[^4]是否与用户提供的引用内容匹配,避免混淆。</think>### Oracle CONNECT BY 用法详解 #### 一、基本概念 CONNECT BYOracle 数据库用于处理**层次化查询**的核心语法,常用于树形结构数据遍历(如组织架构、分类层级)。其标准语法为: ```sql SELECT ... FROM 表名 START BY 根节点条件 CONNECT BY 父节点与子节点关系 [NOCYCLE] WHERE 结果过滤条件 ``` #### 二、关键组件解析 1. **START BY** 定义层次化查询的**起始节点**。例如: ```sql START BY manager_id IS NULL -- 从最高层管理者开始遍历 ``` 2. **CONNECT BY** 定义父子节点关系,必须使用 `PRIOR` 关键字标记父节点。例如: $$ \text{CONNECT BY PRIOR 子节点列 = 父节点列} $$ ```sql CONNECT BY PRIOR employee_id = manager_id -- 员工ID与上级ID关联 ``` 3. **层级函数与伪列** - `LEVEL`:表示当前节点层级(根节点为1) - `CONNECT_BY_ROOT`:获取根节点字段值 - `SYS_CONNECT_BY_PATH`:显示完整路径 示例: ```sql SELECT employee_name, LEVEL, SYS_CONNECT_BY_PATH(employee_name, '/') AS path FROM employees ``` 4. **循环处理** 当数据存在循环引用时,需添加 `NOCYCLE` 参数,并通过 `CONNECT_BY_ISCYCLE` 检测循环[^1][^3]。例如: ```sql SELECT employee_name, CONNECT_BY_ISCYCLE AS is_cycle FROM employees CONNECT BY NOCYCLE PRIOR employee_id = manager_id ``` #### 三、完整示例教程 1. **创建测试表** ```sql CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(50), manager_id NUMBER ); INSERT INTO employees VALUES (1, 'CEO', NULL); INSERT INTO employees VALUES (2, 'CTO', 1); INSERT INTO employees VALUES (3, '工程师A', 2); INSERT INTO employees VALUES (4, '工程师B', 2); ``` 2. **基础层次查询** ```sql SELECT employee_name, LEVEL, SYS_CONNECT_BY_PATH(employee_name, '->') AS hierarchy_path FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; ``` **输出结果**: ``` CEO 1 ->CEO CTO 2 ->CEO->CTO 工程师A 3 ->CEO->CTO->工程师A 工程师B 3 ->CEO->CTO->工程师B ``` 3. **循环数据检测示例** ```sql -- 制造循环数据 UPDATE employees SET manager_id = 3 WHERE employee_id = 2; -- 带循环检测的查询 SELECT employee_name, CONNECT_BY_ISCYCLE AS cycle_flag FROM employees START WITH employee_id = 1 CONNECT BY NOCYCLE PRIOR employee_id = manager_id; ``` **输出结果**: ``` CEO 0 CTO 0 工程师A 1 -- 标记存在循环 ``` #### 四、执行顺序注意 1. 先处理 `CONNECT BY` 构建层次结构 2. 再应用 `WHERE` 子句过滤结果 3. 最后执行 `SELECT` 字段计算
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值