欢迎光临
我们一直在努力

‌Oracle CTE(公共表表达式)

‌Oracle CTE(公共表表达式)是用 WITH 关键字定义的临时结果集,只在当前 SQL 语句中生效,可简化复杂查询和实现递归查询‌。‌‌‌
基本语法

    ‌标准 CTE 结构‌:用 WITH 开头定义临时表名,AS 后面跟查询语句,主查询直接引用该表名。
        格式:WITH 表名 AS (SELECT 语句) SELECT * FROM 表名
        临时表只在当前 SQL 执行时存在,执行完自动消失。
        可定义多个 CTE,用逗号分隔。‌‌‌
    ‌优势‌:把嵌套子查询拆成线性逻辑,代码更好读好维护。
        避免重复写相同子查询,提升复用性。
        复杂报表可拆成多个有业务语义的小模块。
        替代临时视图,不需要永久存储时使用。‌‌‌

递归查询

    ‌递归 CTE 结构‌:由锚成员(初始查询)和递归成员两部分组成,用 UNION ALL 连接。
        锚成员:先执行,查出顶级节点或种子数据。
        递归成员:引用 CTE 自身,基于前一次结果继续查询。
        终止条件:递归部分不再产生新行时自动停止。‌‌‌
    ‌典型应用场景‌:处理树形或层次结构数据。
        ‌组织架构查询‌:查某个员工的所有下属,包括间接下属。
        ‌目录树遍历‌:产品分类、菜单层级等。
        ‌序列生成‌:生成日期序列、数字序列。
        示例:生成 1 到 10 的整数序列 WITH t1(n) AS (SELECT 1 FROM dual UNION ALL SELECT n+1 FROM t1 WHERE n<10) SELECT * FROM t1‌‌‌
    ‌注意事项‌:递归 CTE 必须包含明确终止条件,否则可能无限循环。
        处理大量数据时可能影响性能,需合理使用索引。
        Oracle 从 11g R2 版本开始支持递归 CTE。‌‌‌

和 CONNECT BY 有啥区别

    ‌CTE 特点‌:更符合 SQL 标准,跨数据库兼容性好。
        支持更复杂的递归逻辑,如条件过滤、聚合计算。
        可在递归过程中使用聚合函数(SUM、COUNT 等)。
        适合跨数据库开发,保证代码兼容性。‌‌‌

    ‌CONNECT BY 特点‌:Oracle 专有语法,不具备跨数据库移植性。
        语法更简洁,用 START WITH 和 CONNECT BY 子句。
        可通过 LEVEL 伪列直接获取节点深度。
        支持 SYS_CONNECT_BY_PATH 函数直接生成节点路径。
        对大规模层级数据可能性能更好。‌‌‌

    ‌选择建议‌:需要跨数据库兼容或复杂递归逻辑时用 CTE,纯 Oracle 环境且简单层级查询可用 CONNECT BY。‌‌‌

赞(0)
未经允许不得转载:171主机测评 » ‌Oracle CTE(公共表表达式)
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址