Oracle 数据层级查询

前言

在生产环境中，经常会遇到数据校验和治理工作，其中有一些表存储了单位，组织机构等存在上下级关系的数据，通常表没有完整的 path 字段，需要来连接数据库手动查询。

1、示例表 建表语句，并插入数据

-- 创建一个名为 your_table 的表
CREATE TABLE your_table (
  id VARCHAR2(32) NOT NULL,
  parent_id VARCHAR2(32),
  name VARCHAR2(100));
-- 添加注释
COMMENT ON TABLE your_table IS '单位表';
COMMENT ON COLUMN your_table.id IS '单位ID';
COMMENT ON COLUMN your_table.parent_id IS '上级单位ID';
COMMENT ON COLUMN your_table.name IS '单位名称';
 
-- 模拟数据
INSERT INTO your_table (id, parent_id, name) VALUES ('000000001', NULL, '顶级单位');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000002', '000000001', '一级单位A');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000003', '000000001', '一级单位B');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000004', '000000002', '二级单位A1');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000005', '000000002', '二级单位A2');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000006', '000000003', '二级单位B1');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000007', '000000003', '二级单位B2');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000008', '000000004', '三级单位A1-1');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000009', '000000004', '三级单位A1-2');
INSERT INTO your_table (id, parent_id, name) VALUES ('000000010', '000000006', '三级单位B1-1');

2、层级数据查询sql

------level--层级  sys_connect_by_path(name,'/') --固定格式的函数，这个是显示层级路径的，一般只查询ID和name
select id 单位ID,PARENT_ID 上级单位ID,level 层级,sys_connect_by_path(name,'/') 单位名称全路径,sys_connect_by_path(ID,'/') 单位ID层级   --查询的字段
  from your_table   --查询哪一张表
   start with ID = '000000001'  --开始条件（一般是最上级ID）
   connect by prior id=PARENT_ID ; --递归条件（id=PARENT_ID顺序查询 ,PARENT_ID=id 倒叙查询，根据 start with ID 开始条件反向查询）

查询结果展示