• oracle递归查询（start with connect by prior）以及 树形统计connect_by_root（子节点汇总到父节点）

    oracle递归查询（start with connect by prior）以及 树形统计connect_by_root（子节点汇总到父节点）

    1. 准备表和数据

    1.1 建表

    • 如下：

      -- 公司/部门表

drop table sys_company_dept;

create table sys_company_dept
(
  dept_id varchar2(20),
  dept_name varchar2(50),
  parent_id varchar2(20)
);
alter table SYS_COMPANY_DEPT
add constraint key_dept_id primary key (DEPT_ID);

comment on table SYS_COMPANY_DEPT
  is '公司和部门表';
comment on column SYS_COMPANY_DEPT.dept_id
  is '公司(部门)id';
comment on column SYS_COMPANY_DEPT.dept_name
  is '公司(部门)名';
comment on column SYS_COMPANY_DEPT.parent_id
  is '父id';

-- 用户表

drop table sys_user;

create table sys_user
(
  user_id varchar2(20),
  user_name varchar2(50),
  dept_id varchar2(20),
  salary number(5)
);
alter table sys_user
add constraint key_user_id primary key (user_id);

comment on table sys_user
  is '用户表';
comment on column sys_user.user_id
  is '用户id';
comment on column sys_user.user_name
  is '用户名';
comment on column sys_user.dept_id
  is '部门id';
comment on column sys_user.salary
  is '薪水';
  

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47

    1.2 造数

    • 如下：
      insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('A000', 'XXX科技有限公司', null);

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('B001', '郑州分公司', 'A000');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('B002', '厦门分公司', 'A000');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('B003', '武汉分公司', 'A000');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('B004', '成都分公司', 'A000');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('B005', '南京分公司', 'A000');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C001', '郑州-科技部', 'B001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C002', '郑州-业务部', 'B001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C003', '郑州-人事部', 'B001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C004', '郑州-财务部', 'B001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('D001', '郑州-科技部-开发部', 'C001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('D002', '郑州-科技部-测试部', 'C001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('D003', '郑州-科技部-需求分析部', 'C001');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C005', '厦门-科技部', 'B002');

insert into sys_company_dept (DEPT_ID, DEPT_NAME, PARENT_ID)
values ('C006', '武汉-财务部', 'B003');
	
-- -----------------------------------------
insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1001', '大佬', 'A000', 0);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1002', '二佬', 'A000', 80000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1003', '郑董事', 'B001', 70000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1004', '郑科技', 'C001', 60000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1005', '郑开发1', 'D001', 50000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1006', '郑开发2', 'D001', 51000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1007', '郑开发3', 'D001', 52000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1008', '郑测试1', 'D002', 40000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1009', '郑测试2', 'D002', 40000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1010', '郑需求', 'D003', 45000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1011', '郑业务', 'C002', 30000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1012', '郑人事', 'C003', 30000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1013', '郑财务', 'C004', 30000);

insert into sys_user (USER_ID, USER_NAME, DEPT_ID, SALARY)
values ('1014', '厦公司', 'B002', 60000);

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53
      • 54
      • 55
      • 56
      • 57
      • 58
      • 59
      • 60
      • 61
      • 62
      • 63
      • 64
      • 65
      • 66
      • 67
      • 68
      • 69
      • 70
      • 71
      • 72
      • 73
      • 74
      • 75
      • 76
      • 77
      • 78
      • 79
      • 80
      • 81
      • 82
      • 83
      • 84
      • 85
      • 86
      • 87

    1.3 查询造数

    • 如下：
      在这里插入图片描述

      在这里插入图片描述

    2. 递归查询（start with ……）

    2.1 递归查询子节点

    2.1.1 查询父下的所有子节点（不包括父）

    • 如下：
      在这里插入图片描述
      -- 不包括起始父节点
select * from sys_company_dept  
start with parent_id='B001' connect by prior dept_id=parent_id;

      • 1
      • 2
      • 3

    2.1.2 查询父下的所有子节点（包括父）

    • 不同的是：start with dept_id ，如下：
      在这里插入图片描述
    • start with 可以放后面，如下：
      -- 包括起始父节点
select * from sys_company_dept  
start with dept_id='B001' connect by prior dept_id=parent_id;

select * from sys_company_dept  connect by prior dept_id=parent_id
start with dept_id='B001' ;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    2.2 递归查询父节点

    2.2.1 不添加其他where条件

    • 如下：
      在这里插入图片描述
      select * from sys_company_dept  
start with dept_id='D003' 
connect by prior parent_id=dept_id;

      • 1
      • 2
      • 3
    • 与查询子节点的区别是：
      connect by prior 后面跟的是 dept_id 还是 parent_id，这是二者区别的关键

    2.2.2 添加其他where条件

    • 如下：
      在这里插入图片描述
      select * from sys_company_dept  
where 1=1 and parent_id='A000'
start with dept_id='D003' 
connect by prior parent_id=dept_id;

      • 1
      • 2
      • 3
      • 4

    3. 树形结构查询

    3.1 level (树形结构查询)

    • 如下：
      在这里插入图片描述
      select (rpad(' ', 2*(level-1), '-|' ) || dept_name) dept_name,level,dept_id,parent_id
FROM sys_company_dept
start with parent_id is null
connect by prior dept_id = parent_id;

      • 1
      • 2
      • 3
      • 4

    3.2 sys_connect_by_path (树形结构查询)

    • 效果如下：
      在这里插入图片描述
    • 查询语句如下：
      select (rpad(' ', 2*(level-1), '-|' ) || dept_name) dept_name, 
connect_by_root dept_name as root_dept_name, 
connect_by_isleaf, level , 
sys_connect_by_path(dept_name, '/')
FROM sys_company_dept
start with parent_id is null
connect by prior dept_id = parent_id;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    • 简单介绍
      1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
      2. CONNECT_BY_ISLEAF 判断是否为叶子节点（0-否，1-是），如果这个节点下面有子节点，则不是叶子节点 ，否则为叶子节点
      3. LEVEL 伪列表示节点深度
      4. SYS_CONNECT_BY_PATH 函数显示详细层次路径，并用“/”分隔

    3.3 CONNECT_BY_ROOT(树形层次结构查询)

    3.3.1 列出每个根节点的子孙

    • 继续使用 CONNECT_BY_ROOT 可以得到每个分支的根节点信息，如下：
      在这里插入图片描述
    • sql语句如下：
      select t.dept_id,t.dept_name,connect_by_root(t.dept_id) root_id,rownum row_num
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id;

      • 1
      • 2
      • 3
      • 4

    3.3.2 列出-部门(子部门）和公司对应

    • 首先，我这里有个字段，type（1-公司 0-部门）
    • 再看原始数据：
      在这里插入图片描述
    • 看效果：
      在这里插入图片描述
    • 给出sql语句：
      with temp1 as(
select t.dept_id,t.dept_name,t.type,connect_by_root(t.dept_id) root_id
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
)
select t1.dept_id,t1.dept_name,t1.root_id as company_id,t2.dept_name as company_name,
(case t1.type when '1' then '公司' else '部门' end)type
from temp1 t1 
left join sys_company_dept t2 on t1.root_id = t2.dept_id
where t2.type='1' and t2.dept_id!='A000';

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

    4. 树形统计（子节点汇总到父节点）

    4.1 统计各公司下部门的数量

    • 统计公司/部门下有多少部门，包括根节点（因为要汇总到根上），如下：
      在这里插入图片描述
    • 统计对不对，不妨对比一下数据
      在这里插入图片描述
      通过对比可知，统计的没有问题
    • sql如下：
      --  统计
select t2.root_id,root_name,count(0) from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
)t2 group by t2.root_id,root_name;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    4.2 统计部门下的人数（子向父汇总）

    4.2.1 统计这个大公司

    • 再上面语法都了解的基础上，我们再写这个统计就很简单了，如下：
      在这里插入图片描述

    • 检查一下统计的是否正确
      在这里插入图片描述
      在这里插入图片描述

    • sql语句-两种写法
      第①种写法：

      select root_id,root_name,sum(sum_user) sum_user from (
select user_id,temp1.dept_id,(case when user_id is null then 0 else 1 end) sum_user,
root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)temp2
group by root_id,root_name;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12

      第②种写法：

      with temp2 as(
select user_id,temp1.dept_id,(case when user_id is null then 0 else 1 end) sum_user,root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)
select root_id,root_name,sum(sum_user) sum_user from temp2 group by root_id,root_name;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

    4.2.2 统计某个子公司下的或者某个部门下的

    • 根据参数dept_id控制，如下：
      在这里插入图片描述
    • sql如下：
      select root_id,root_name,sum(sum_user) sum_user from (
select user_id,temp1.dept_id,(case when user_id is null then 0 else 1 end) sum_user,
root_id,root_name
from (
select t0.dept_id,t0.dept_name,
connect_by_root(t0.dept_id) root_id,connect_by_root(t0.dept_name) root_name 
from (
select t.dept_id,t.dept_name,t.parent_id
from sys_company_dept t 
start with t.dept_id ='B001' -- D001 B001
connect by prior t.dept_id = t.parent_id)t0
start with t0.dept_id in(select dept_id from sys_company_dept)
connect by prior t0.dept_id = t0.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)temp2
group by root_id,root_name;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16

    4.3 统计部门下的人的薪水和

    • 如下图：
      在这里插入图片描述
    • 检验统计是否准确
      在这里插入图片描述
    • sql语句：
      with temp2 as(
select temp1.dept_id,nvl(u.salary, 0) sum_salary,root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)
select root_id,root_name,sum(sum_salary) sum_salary from temp2 group by root_id,root_name;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
    • 好了，到此结束，完美！

    5. 附整理所有查询sql

    • 如下：
      -- 查子节点
select * from sys_company_dept  
start with parent_id='A000' 
connect by prior dept_id=parent_id;

select * from sys_company_dept  
start with dept_id='A000' 
connect by prior dept_id=parent_id;

-- 根据部门 id 查所有父节点
select * from sys_company_dept  
start with dept_id='D003' 
connect by prior parent_id=dept_id;

-- 查他爹的公司

select * from sys_company_dept  
where 1=1 and parent_id='A000'
start with dept_id='D003' 
connect by prior parent_id=dept_id;



-------树形结构查询 1
select (rpad(' ', 2*(level-1), '-|' ) || dept_name) dept_name,
level,dept_id,parent_id
FROM sys_company_dept
start with parent_id is null
connect by prior dept_id = parent_id;


-------树形结构查询 2

select (rpad(' ', 2*(level-1), '-|' ) || dept_name) dept_name, 
connect_by_root dept_name as root_dept_name, 
connect_by_isleaf, level , 
sys_connect_by_path(dept_name, '/')
FROM sys_company_dept
start with parent_id is null
connect by prior dept_id = parent_id;

-- ----------------------
select t.dept_id,t.dept_name,connect_by_root(t.dept_id) root_id,rownum row_num
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id;

--  统计 子部门个数
select t2.root_id,root_name,count(0) from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
)t2 group by t2.root_id,root_name;

-- 统计 各公司的人员人数

-- 统计人数 1法
select root_id,root_name,sum(sum_user) sum_user from (
select user_id,temp1.dept_id,(case when user_id is null then 0 else 1 end) sum_user,
root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)temp2
group by root_id,root_name;
-----统计人数 2法
with temp2 as(
select user_id,temp1.dept_id,(case when user_id is null then 0 else 1 end) sum_user,root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)
select root_id,root_name,sum(sum_user) sum_user from temp2 group by root_id,root_name;

-- 统计 各公司的人员薪水总和
with temp2 as(
select temp1.dept_id,nvl(u.salary, 0) sum_salary,root_id,root_name
from (
select t.dept_id,t.dept_name,
connect_by_root(t.dept_id) root_id,connect_by_root(t.dept_name) root_name
from sys_company_dept t 
start with t.dept_id in(select dept_id from sys_company_dept)
connect by prior t.dept_id = t.parent_id
) temp1 left join sys_user u on temp1.dept_id = u.dept_ID
)
select root_id,root_name,sum(sum_salary) sum_salary from temp2 group by root_id,root_name;


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53
      • 54
      • 55
      • 56
      • 57
      • 58
      • 59
      • 60
      • 61
      • 62
      • 63
      • 64
      • 65
      • 66
      • 67
      • 68
      • 69
      • 70
      • 71
      • 72
      • 73
      • 74
      • 75
      • 76
      • 77
      • 78
      • 79
      • 80
      • 81
      • 82
      • 83
      • 84
      • 85
      • 86
      • 87
      • 88
      • 89
      • 90
      • 91
      • 92
      • 93
      • 94
      • 95
      • 96
      • 97
  • 相关阅读:
    slurm集群搭建
    关于YAML配置
    mybatis中有哪些执行器(Executor)呢?
    C++——vector容器的基本使用和模拟实现
    表单引擎字段类型该如何设计？
    一文读懂面试官都在问的Fastjson漏洞
    Apache Hudi在信息服务行业构建流批一体的实践
    【计算机网络笔记六】应用层（三）HTTP 的 Cookie、缓存控制、代理服务、短连接和长连接
    软件工程毕业设计课题（8）基于python的毕业设计python共享图片分享系统毕设作品源码
    设计模式行为型-状态模式
  • 原文地址：https://blog.csdn.net/suixinfeixiangfei/article/details/126898884