Ora2pg之树形结构查询转换

spring data mongodb 大数据量查询性能差的原因(20s 优化到2s) 阅读详情

Oracle树形结构查询转Postgresql总结

一、基本语法

--prior在子id一侧
select * from table [start with condition1]
    connect by [prior] id=parentid

一般用来查找存在父子关系的数据,也就是树形结构的数据;其返还的数据也能够明确的区分出每一层的数据。

  • start with condition1 是用来限制第一层的数据,或者叫根节点数据;以这部分数据为基础来查找第二层数据,然后以第二层数据查找第三层数据以此类推。

  • connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找;比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配,如果这个条件成立那么查找出来的数据就是第二层数据,同理查找第三层第四层…等等都是按这样去匹配。

--prior在父id一侧
select * from table [start with condition1]
    connect by id= [prior] parentid

这种用法就表示从下往上查找数据,可以理解为从叶子节点往上查找父级几点,用第一层数据的parentid去跟表记录里面的id进行匹配,匹配成功那么查找出来的就是第二层数据;上面的那种就是从父级节点往下查找叶子节点。

二、LEVEL

level关键字,代表树形结构中的层级编号;第一层是数字1,第二层数字2,依次递增。

SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,level,to_char(salary,'999')
    FROM emp tt where level>1 START WITH lead_id = 0 connect BY prior tt.emp_id = lead_id 

三、CONNECT_BY_ROOT方法

CONNECT_BY_ROOT方法,能够获取第一层集结点结果集中的任意字段的值;例CONNECT_BY_ROOT(字段名)。

select t.*, level, CONNECT_BY_ROOT(emp_id)
  from emp t
 start with t.emp_id = 1
connect by prior t.emp_id = t.lead_id;

四、CONNECT_BY_ISCYCLE、NOCYCLE

CONNECT_BY_ISCYCLE伪列,如果当前行有一个子行,且子行又是当前行的祖先行,CONNECT_BY_ISCYCLE返回1,否则返回0.
只有在CONNECT BY从句中指定了NOCYCLE参数,才能指定CONNECT_BY_ISCYCLE。由于CONNECT BY存在循环数据,NOCYCLE能使Oracle返回查询结果,否则将查询失败。

SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,CONNECT_BY_ISCYCLE
    FROM emp tt where level>1 START WITH lead_id = 0 connect BY NOCYCLE prior tt.emp_id = lead_id; 

五、CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF表示是不是叶子节点。

SELECT 1 as con_1,'2' as con_2,tt.emp_id,lead_id,emp_name,salary,level,CONNECT_BY_ISLEAF
    FROM emp tt where level>1 START WITH lead_id = 0 connect BY prior tt.emp_id = lead_id 

六、SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示。

select SYS_CONNECT_BY_PATH(emp_name, '>') AS chain
  from emp 
start with emp_id =1
connect by  lead_id = prior emp_id;

七、ORDER SIBLINGS BY表达式

在层次查询中如需对亲兄弟的先后顺序进行限定,就必须要使用特有的“ORDER SIBLINGS BY”

select emp_id,emp_name, SYS_CONNECT_BY_PATH(emp_id, '>') AS chain
  from emp 
start with emp_id =1
connect by  lead_id = prior emp_id
order siblings by emp_id desc;

八、转postgresql

  • 转换SYS_CONNECT_BY_PATH函数
--oracle
SELECT emp_id,lead_id,emp_name,SYS_CONNECT_BY_PATH (emp_name,'/') PATH
FROM emp
START WITH  (emp_id = 1) 
CONNECT BY   (PRIOR emp_id = lead_id);

--pg
with recursive cte(emp_id, lead_id, emp_name, path) as
(
 select emp_id,
         lead_id,
         emp_name,
         '/' || emp_name
  from   emp e
  where  emp_id = 1
  union all
  select c.emp_id,
         c.lead_id,
         c.emp_name,
         concat_ws('/', p.path, c.emp_name)
  from emp c
	join cte p on p.emp_id = c.lead_id
) select e.*  FROM cte e;
  • 转换CONNECT_BY_ISLEAF
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ISLEAF  ISLEAF  
FROM emp
START WITH  (emp_id = 1) 
CONNECT BY   (PRIOR emp_id = lead_id);

--pg
with recursive cte(emp_id, lead_id, emp_name, visited) as
(
 select emp_id,
         lead_id,
         emp_name,
         array[emp_id] as visited
  from   emp e
  where  emp_id = 1
  union all
  select c.emp_id,
         c.lead_id,
         c.emp_name,
         p.visited || c.emp_id
  from emp c
	join cte p on p.emp_id = c.lead_id
	where c.emp_id <> all(p.visited)
) select emp_id,lead_id,emp_name,
       not exists (select * from cte pr where pr.lead_id = e.emp_id) as is_leaf  FROM cte e;
       
--pg

WITH RECURSIVE Tab_HH__1 AS (
SELECT emp_id,lead_id,emp_name, array[emp_id] as visited  FROM EMP WHERE (emp_id::numeric=1)  
UNION ALL 
SELECT Tab_HH__2.emp_id,Tab_HH__2.lead_id,Tab_HH__2.emp_name, Tab_HH__1.visited || Tab_HH__2.emp_id as visited   FROM (
SELECT emp_id,lead_id,emp_name  FROM EMP
) Tab_HH__2 INNER JOIN Tab_HH__1 ON (Tab_HH__1.emp_id=Tab_HH__2.lead_id) 	where Tab_HH__2.emp_id <> all(Tab_HH__1.visited)   
)
SELECT emp_id,lead_id,emp_name,(case when not exists (select * from Tab_HH__1 p where p.lead_id = Tab_HH__1.emp_id) then 1 else 0 end )as is_leaf FROM Tab_HH__1
 
 
--pg


WITH RECURSIVE Tab_HH__1 AS (
SELECT emp_id,lead_id,emp_name, array[emp_id] as visited  FROM EMP WHERE (emp_id::numeric=1)  
UNION ALL 
SELECT Tab_HH__2.emp_id,Tab_HH__2.lead_id,Tab_HH__2.emp_name, Tab_HH__1.visited || Tab_HH__2.emp_id as visited   FROM (
SELECT emp_id,lead_id,emp_name  FROM EMP
) Tab_HH__2 INNER JOIN Tab_HH__1 ON (Tab_HH__1.emp_id=Tab_HH__2.lead_id) 	where Tab_HH__2.emp_id <> all(Tab_HH__1.visited)   
)
SELECT emp_id,lead_id,emp_name,
(case  when  not exists (select * from Tab_HH__1  p where p.lead_id = Tab_HH__1.emp_id)    then 1 else 0 end ) as is_leaf ,
(case  when  not exists (select * from Tab_HH__1  p where p.lead_id = Tab_HH__1.emp_id)    then 1 else 0 end )||'-'||emp_name  as aaaaa 
FROM Tab_HH__1 order by is_leaf
 



       
  • 转换CONNECT_BY_ROOT
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ROOT(emp_id) as root_id
FROM emp
START WITH  (emp_id = 1) 
CONNECT BY   (PRIOR emp_id = lead_id);

--pg
with recursive cte(emp_id, lead_id, emp_name, root_id) as
(
 select emp_id,
         lead_id,
         emp_name,
         emp_id as root_id
  from   emp e
  where  emp_id = 1
  union all
  select c.emp_id,
         c.lead_id,
         c.emp_name,
         p.root_id
  from emp c
	join cte p on p.emp_id = c.lead_id

) select emp_id,lead_id,emp_name, root_id FROM cte e;
  • 转换NOCYCLE
--oracle
SELECT emp_id,lead_id,emp_name
FROM emp
START WITH  (emp_id = 1) 
CONNECT BY NOCYCLE    (PRIOR emp_id = lead_id);

--pg
with recursive cte(emp_id, lead_id, emp_name, visited) as
(
 select emp_id,
         lead_id,
         emp_name,
         array[emp_id] as visited
  from   emp e
  where  emp_id = 1
  union all
  select c.emp_id,
         c.lead_id,
         c.emp_name,
         p.visited || c.emp_id as visited
  from emp c
	join cte p on p.emp_id = c.lead_id
	where c.emp_id <> all(p.visited)
) select emp_id,lead_id,emp_name FROM cte e;
  • 转换CONNECT_BY_ISCYCLE
--oracle
SELECT emp_id,lead_id,emp_name,CONNECT_BY_ISCYCLE as cycle
FROM emp
START WITH  (emp_id = 1) 
CONNECT BY NOCYCLE    (PRIOR emp_id = lead_id);
--pg
with recursive cte(emp_id, lead_id, emp_name, visited,cycle) as
(
 select emp_id,
         lead_id,
         emp_name,
         array[emp_id] as visited,
				 false  as cycle     -- 是否循环(初始为否)
  from   emp e
  where  emp_id = 1
  union all
  select c.emp_id,
         c.lead_id,
         c.emp_name,
         p.visited || c.emp_id as visited,
				 (c.emp_id = ANY(p.visited || c.emp_id)) as cycle     -- 是否循环,判断新点是否已经在之前的路径中 
  from emp c
	join cte p on p.emp_id = c.lead_id
	where c.emp_id <> all(p.visited)  --nocycle
) select emp_id,lead_id,emp_name,cycle FROM cte e;

PostgreSQL树形结构的递归查询示例 背景 处理不确定深度的层级结构,比如组织机构,一个常用的设计是在一张表里面保存 ID 和 Parent_ID ,并且通过自联结的办法构造一颗树。这种方式对写数据的过程很友好,但是查询过程就变得相对复杂。在不引入MPTT模型的前提下,必须通过递归算法来查询某个节点和下级子节点。 Oracle提供的connect by扩展语法,简单好用。但是其他的RDBMS就没这么人性化了(或者我不知道)。最近在项目中使用PostgreSQL来查询树形数据,记录一下。 构造样本数据 drop table if exists demo.tree_data; create table demo.tree_d 立即下载

相关推荐

openEuler 22.03 安装及配置Gitlab容器

在 openEuler 22.03 上安装及配置Gitlab容器

轻步玉阶 3319

Postgresql兼容Oracle改造系列——START WITH/CONNECT BY

Oracle分层查询 Oracle中START / WITH / CONNECT BY提供分层查询的能力,从START WITH开始遍历记录,递归查询结果集直到拿到所有满足条件的结果。 例如下面测试数据: drop table sr_menu; create table sr_menu( id number(10) not null, parent number(10), title varchar2(50) ); insert into sr_menu values (1, null,

InternalsOf 1万+

视频教程-Ps教程之神奇的Photoshop自动化技术-Photoshop

Ps教程之神奇的Photoshop自动化技术 10年以上IT行业工作经验、三...

weixin_28812857的博客 1477

postgresql etc功能改写Oracle start with connect by递归功能

目前已做修改的三种例子: 1、start with connect by改写 SELECT t.* FROM pub_organ t, ( SELECT organ_id FROM ( –原来的写法 – SELECT – p.organ_id, – p.organ_type – FROM – pub_organ p START WITH p.organ_id = ‘370000’ CONNECT BY PRIOR p.organ_id = p.parent_id 改写成PG的: with

杀不死我的终将使我强大 4141

从零开始:用ADS 2023手把手教你设计2.4GHz Wi-Fi LNA(基于ATF-54143,附模型文件)

本文详细介绍了如何使用ADS 2023软件设计2.4GHz Wi-Fi频段的低噪声放大器(LNA),基于ATF-54143晶体管。内容涵盖模型导入、直流工作点确定、稳定性优化、噪声匹配等关键步骤,并提供可下载的模型文件包,帮助工程师快速掌握LNA设计技巧。

weixin_42531128的博客 324

单细胞分析进阶:sctransform标准化在Seurat中的实战解析

本文深入解析了sctransform标准化方法在Seurat单细胞分析中的实战应用。通过对比传统对数标准化,sctransform能更有效地建模并剔除技术噪音,提升数据质量。文章提供了从环境配置、数据准备、核心参数解析到完整流程整合的详细指南,并针对常见问题给出解决方案,帮助研究者获得更清晰的细胞分群和更可靠的差异表达结果。

weixin_29248313的博客 153

pgsql 实现oracle的start with与层级树level

oracle SELECT LEVEL AS LVL, TPL.PORT_ID, TPL.PARENT_PORTID FROM TN_PONLINK TPL START WITH TPL.DEV_ID = 'F62A1470D64111E6990CAC577F000001' CONNECT BY PRIOR

0xadf7Dc19D52eD986AB6c8727D76fcd7C7ce1435b 4053

杰理的蓝牙芯片的key是什么?以及该如何添加key?杰理key文件原理

很多的工程师,在开发杰理蓝牙芯片的第一步就会有一个很深的疑惑,关于key的 1、什么是key文件? 2、Key文件是如何产生的? 3、为什么需要他key文件? 4、如何添加key文件? 5、以及实际开发中的一些报错该如何处理?

qyvhome的博客 4103

oracle语法start with...connect by... 改为pg语法

其中包括两个数据库中字段默认值不同,比如tprior_node。

ycoder的博客 1372

绕过ROS2!在RDK X5上直接用Python调用海康相机C++ SDK的避坑指南

本文详细介绍了在RDK X5上绕过ROS2,直接使用Python调用海康相机C++ SDK的轻量化方案。通过Cython构建Python绑定,提取海康相机SDK核心组件,实现高效图像采集。文章涵盖环境准备、Cython接口设计、编译系统配置及性能优化技巧,特别适合ARM64平台开发者。

weixin_33712987的博客 251

通用具身智能仿真平台桃源2.0,三行代码定义任务,数采效率最高提升20倍

木木-具身Xbot具身知识库2025年03月13日 17:40浙江在首个“城市级”大规模仿真平台GRUtopia1.0的基础上,升级后的GRUtopia2.0凭借通用模块化框架、场景资产自动化生成、高效数据采集系统三大革新,进一步向通用化、多样化、易用化迈进。仅通过单一平台、简单代码输入,即可实现仿真环境中机器人灵活训练,提升数据采集效率,为研究者提供了“一站式”具身智能开发解决方案。

具身机器人和通用AI 818

pg数据库的一些问题

pg数据库的一些问题 1.UUID的生成 安装扩展 create extension "uuid-ossp" 用uuid_generate_v4()访问

我是小白程序员 768

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

pgsql模拟oracle的start with connect by查询

with t as (select * from t_department where parent_id=0) select count(0) from t; with t1 as (select * from t_department),t2 as(select * from t_department where parent_id=0) select t1.* from t1,t2 where t2.id=t1.parent_id; with recursive t as(select .

gaston的博客 2832

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0, 兼容Win7/Win10/WinXP,解压就可用。

pscad IEEE标准系统搭建说明

pscad IEEE标准系统搭建说明,包含典型的IEEE 9节点、14节点、30节点、39节点和118节点系统。内含标准测试系统的原始数据和潮流结果。

pgsql中的常用函数总结

个人笔记

m0_52941727的博客 4104

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

connect by prior 用法

这个查询将从employee表中选择所有manager_id为100的员工及其直接下属,以及下属的下属,直到找到所有的叶子节点。connect by prior是一种在Oracle数据库中使用的特殊的语法,用于执行递归查询。它表示在查询结果中,每个行都与它的父级行(或祖先行)存在一个关系,可以用于查询树形结构、组织结构、层次结构等数据模型。其中,CONNECT BY PRIOR是关键字,它表示该查询使用connect by prior递归查询语法。CONNECT BY PRIOR <递归条件>

m0_74245888的博客 5415

postgresql 学习笔记

SQL 入门语言 3.1 SQL(Structured Query Lanuage) 特点:它是结构化查询语句,重要的关系型数据库操作语言。PgSQql 的基础语法也同样适用于其他的关系型数据库如:mysql、oracle等 3.1.1 语法分类 Sql命令一般分为DQL、DML、DDL、DCL几类;其中, DQL:数据查询语句,一般是select开始的 DML(Data Manipul...

weixin_41591572的博客 2728

postgresql 递归查询

postgresql 递归查询 select id, parentId, 列名1, 列名2 from 表名 START WITH t1.id= ? CONNECT BY PRIOR t1.ID = t1.PARENTID

weixin_42955681的博客 699

PgSQL——学习笔记八: ORDER BY 子句:排序 & GROUP BY 语句:分组

PostgreSQL ORDER BY 语句:对一列或多列数据进行升序(ASC)或降序(DESC)排列。 在 PostgreSQL 中,ORDER BY 用于对一列或者多列数据进行升序(ASC)或者降序(DESC)排列。 //ORDER BY 子句的基础语法如下: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; 您可以在 ORDER BY 中

ZYF的博客 6788

oracle层级查询connect by和postgres曾经查询with cte as

oracle层级查询connect by和postgres曾经查询with cte as

windandsnowlove的博客 418

sql语句start with connect by prior语法解析

sql语句start with connect by prior语法解析 START WITH CONNECT BY PRIOR用于组织结构查询、省市查询、多级菜单查询(商城系统),通过父级id查询所有的子级、以及通过当前id查询父级。 prior分两种放法: 1 放在子节点端 表示start with 指定的节点作为根节点,按照从上到下的顺序遍历 2 放在父节点端 表示start wit...

qq_39298211的博客 5682

Oracle转PostgreSQL之start with / connect by

Oracle转PostgreSQL之start with / connect by

淮城一只猫 3423

Oracle、postgresql递归语法

虽然这两种方法的语法有所不同,但它们的基本思想是相同的:定义一个起点,然后定义一个递归关系来遍历数据。这个查询由两部分组成,第一部分是基础查询,它定义了递归查询的起点。在数据库中,递归查询是一种特殊的查询,它可以用来查询具有层次结构的数据,例如员工和他们的经理之间的关系,或者目录和子目录之间的关系。例如,可以使用LEVEL来缩进层次结构的输出。这个查询由两部分组成,第一部分是基础查询,它定义了递归查询的起点。在使用这些关键字时,需要注意的是,递归查询可能会导致无限循环,特别是当数据中存在循环引用时。

weixin_73350116的博客 470

模拟Oracle中start with ... connect by的level使用方式(APP)

目录 环境 文档用途 详细信息 环境 系统平台:N/A 版本:4.7.7 文档用途 在Oracle的级联查询中,有时候需要只查询到某一个层级的数据的时候,Oracle使用start with...connect by 与伪列level连用来进行条件筛选,由于瀚高数据库中无start with ... connect by 语法,所以我们需要通过改写的方式来实现这种应用场景。 ...

HighGO 1328

手把手教你 MongoDB 的安装与详细使用(一)

一、什么是MongoDB ? MongoDB 是由C++语言编写的,是一个基于分布式文件存储的开源数据库系统。 在高负载的情况下,添加更多的节点,可以保证服务器性能。 MongoDB 旨在为WEB应用提供可扩展的高性能数据存储解决方案。 MongoDB 将数据存储为一个文档,数据结构由键值(key=&gt;value)对组成。 MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档,数...

weixin_33787529的博客 875

SpringBoot + MongoDB 大容量数据多线程分批处理(示例:抽取字段构建新表)

在实际工作中,我遇到了需要对千万级别数据量的库表进行抽取字段构建新表的需求,于是在此进行总结: 一. 未使用多线程 1. 新建查询类 ExtractInfo package com.example.demo.bean; import java.util.List; public class ExtractInfo { private String tableName; private List<String> fields; public String

qq_38736877的博客 2964
上一篇: Ora2pg之分区索引转换
下一篇: Ora2pg之管道函数转换
codepeter
博客等级 码龄6年 11粉丝 47原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值