oracle之sys_connect_by_path函数

1. sys_connect_by_path 详解 ------------oracle9i 开始有的自带函数（用于解决团队关系等）

语法：
Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行。其语法一般为：
       select ... sys_connect_by_path(column_name,'connect_symbol')  from table 
       start with ... connect by ... prior
理解：
对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，而是start with开始的地方。sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。sys_connect_by_path函数用connect by来寻找下一条记录，直到迭代找不到相应记录为止。概念与递归类似，connect by指定递归(连接)条件，如果条件不满足则递归结束。

1. 查找一个员工的所有下属员工。

start with ename='King' connect by  prior empno=  mgr; 
我是这样理解的：首先数据库中的字段：empno--empname--mgr;这里从'King'开始,把'King'的编号做为管理编号，然后在'King'编号为管理员的员工就是他的下属（循环方式）。

2. 查找一个员工的所有上司经理。

start with ename='King' connect by  prior mgr= empno；
我是这样理解的：首先数据库中的字段：empno--empname--mgr;这里从'King'开始,把'King'的编号做为员工编号，然后在'King'编号的gmr就是他的上司（迭代方式）。

下面是实验：

--CREAT TABLE emp
create table emp ( empno varchar2(5), ename varchar2(8), mgr varchar2(8));
--INSERT DATA
insert into emp (EMPNO, ENAME, MGR)
values ('1', 'jim', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('2', 'tom', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('3', 'tim', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('4', 'lily', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('5', 'mary', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('6', 'tid', '7');
insert into emp (EMPNO, ENAME, MGR)
values ('7', 'King', '10');
insert into emp (EMPNO, ENAME, MGR)
values ('8', 'kenvin', '10');
insert into emp (EMPNO, ENAME, MGR)
values ('9', 'shema', '8');
insert into emp (EMPNO, ENAME, MGR)
values ('10', 'john', '0');
insert into emp (EMPNO, ENAME, MGR)
values ('0', 'root', '');
SELECT * FROM emp;

表结构：

 3.

select sys_connect_by_path(ename,'>') tree from emp start with ename='King' connect by prior empno = mgr;

select sys_connect_by_path(ename,'/') tree from emp start with ename='King' connect by prior empno = mgr; --可以理解为查询king的员工 ‘>’与‘/’只是一个输出格式

 4.

select sys_connect_by_path(ename,'/') tree from emp start with ename='King' connect by empno= prior mgr; --可以理解为查询king的领导 效果与connect by prior mgr= empno相同

select sys_connect_by_path(ename,'/') tree,level from emp start with ename='King' connect by prior mgr= empno;--可以理解为查询king的领导

总结（自己理解）：connect by prior column1 = column2 或者 connect by  column2 = prior column1 写法都可以，

主要看prior写在谁的前面，prior写在谁的前面谁就作为父级值（参照值），也就是说[connect by prior column1 = column2]的意思就是是 按start with 条件查询出的记录行，以column1为参考值，查询column2为column1值的记录。

以上转自：https://www.cnblogs.com/supermandy/p/7873233.html

下面来看一个应用题

有一份数据部分如下，比如：刘备和关羽有关系，说明他们是一个团伙，刘备和张飞也有关系，那么刘备、关羽、张飞归为一个团伙，以此类推。用自己熟悉的编程语言根据人员和相关人员计算出团伙分类；

 

答案1：

建表语句

drop table pop_gg;
create table pop_gg(pop_gg varchar2(10),pop_ggg varchar2(10));

truncate table pop_gg;

insert into pop_gg(pop_gg,pop_ggg) values('刘备','关羽');
insert into pop_gg(pop_gg,pop_ggg) values('刘备','张飞');
insert into pop_gg(pop_gg,pop_ggg) values('张飞','诸葛亮');
insert into pop_gg(pop_gg,pop_ggg) values('曹操','司马');
insert into pop_gg(pop_gg,pop_ggg) values('司马','张辽');
insert into pop_gg(pop_gg,pop_ggg) values('曹操','曹呸');

mysql (已知刘备、曹操为领导的情况）

SELECT DISTINCT 人员, if(人员 ='刘备',1,2) 团队分类 from names_1
WHERE 人员 in ('曹操','刘备')
UNION
SELECT 相关人员 人员,
if(人员='刘备',1,if(人员='曹操',2,(SELECT if(人员='刘备',1,2) FROM names_1 b WHERE b.相关人员=a.人员 ))) 团队分类
FROM names_1 a ORDER BY 团队分类;

Oracle

with tmp as
 (select pop_gg,
         pop_ggg,
         sys_connect_by_path(pop_gg, '/') || '/' || pop_ggg as s,
         substr(sys_connect_by_path(pop_gg, '/') || '/' || pop_ggg,
                1,
                instr(sys_connect_by_path(pop_gg, '/') || '/' || pop_ggg,
                      '/',
                      1,
                      2)) as ss
    from pop_gg
   start with pop_gg in
              (select distinct pop_gg
                 from pop_gg
                where pop_gg not in (select pop_ggg from pop_gg)) -- 求出来父级
  connect by prior pop_ggg = pop_gg
  union all -- 拼接父级
  select distinct pop_gg, pop_gg, '/' || pop_gg || '/', '/' || pop_gg || '/'
    from pop_gg
   where pop_gg not in (select pop_ggg from pop_gg))
select --pop_gg, 
 pop_ggg,
 -- ss,
 dense_rank() over(order by ss asc) as td
  from tmp;

 

 

 转自：​​​​​​团伙分类：有一份数据部分如下，比如：刘备和关羽有关系，说明他们是一个团伙_科技向善-CSDN博客_sql团伙


广州某科技公司面试题 ——wang_Captain_DUDU的博客-CSDN博客