Summary_Oracle 根据父节点查询子节点(递归查询)

本文介绍如何使用SQL的START WITH...CONNECT BY PRIOR语法来查询树形结构数据,并提供了MySQL中的具体实现方法,包括创建表、插入数据、递归查询等步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、语法结构

SELECT ... FROM    + 表名  START WITH + 条件1 CONNECT BY PRIOR + 条件2  WHERE  + 条件3

条件1:是根节点的限定语句,当然可以放宽限定条件,以取得多个根节点,也就是多棵树;在连接关系中,除了可以使用列明外,还允许使用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树形结构的根节点。若该子句省略,则表示所有满足查询条件的行作为根节点。

条件2:是连接条件,其中用PRIOR表示上一条记录,例如CONNECT BY PRIOR STUDENT_ID = GRADE_ID,意思就是上一条记录的STUDENT_ID是本条记录的GRADE_ID,即本记录的父亲是上一条记录。CONNECT BY子句说明每行数据将是按照层次顺序检索,并规定将表中的数据连入树形结构的关系中。PRIOR运算符必须放置在连接关系的2列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构的顺序是自顶向下,还是自底向上。

条件3:是过滤条件,用于对返回的记录进行过滤。

注意:

1,CONNECT BY PRIOR是结构化查询中用到的;

2,START WITH... CONNECT BY PRIOR...的作用,简单来说,就是将一个树状结构存储在一张表里。

二、应用场景

START WITH... CONNECT BY PRIOR...常见的用法,是用来遍历含有父子关系的表结构中。比如省市关系,一个省下面包含多个城市,如果城市基本信息表中,包含有属于哪个省级的字段,那么如果要遍历所有的城市,我们就可以使用START WITH... CONNECT BY PRIOR...。

三、MySQL实现Start With...Connect By Prior 递归查询

(1)、创建表

        create table treeList(
               id varchar(10), -- 节点ID
               name varchar(10), -- 节点名称
               pId varchar(10) -- 父ID

        );

(2)、插入数据

        insert into treeList values(1,'中国',null);
        insert into treeList values(2,'北京',1);
        insert into treeList values(3,'上海',1);
        insert into treeList values(4,'深圳',1);
        insert into treeList values(5,'海淀',2);
        insert into treeList values(6,'朝阳',2);
        insert into treeList values(7,'昌平',2);

        insert into treeList values(8,'丰台',2);

        Oracle中根据父节点查询子节点:

        SELECT * FROM treeList START WITH ID IN (1) CONNECT BY PRIOR ID=PID;

        

(3)、创建函数 getChildList 查询子节点(包含父节点)

        CREATE FUNCTION getChildList (rootId VARCHAR(100)) -- rootId为要查询的节点
        RETURNS VARCHAR(1000)
        BEGIN
                DECLARE pTemp VARCHAR(1000); -- 定义两个临时变量 
                DECLARE cTemp VARCHAR(1000); -- 定义两个临时变量

                SET pTemp = '';  
                SET cTemp = rootId;

                WHILE cTemp is not null DO  
                       if (pTemp = '') then
                                 SET pTemp = cTemp;
                       elseif(pTemp <> '') then
                                 SET pTemp = concat(pTemp,',',cTemp); -- 所有节点连接成字符串
                       end if;
                       SELECT group_concat(
id) INTO cTemp FROM treeList  WHERE FIND_IN_SET(pId,cTemp)>0; 
                 END WHILE;  
                 RETURN pTemp;  

        END;

(4)、执行方法

使用我们直接利用Find_In_Set函数配合这个getChildList函数

        (a)、SELECT getChildList(1) FROM dual;

        

        (b)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(1));

        

                注意:可以嵌套使用

                SELECT * FROM treeList WHERE id in(
                SELECT id FROM treeList WHERE FIND_IN_SET(
id, getChildList(1))

                );

                

        (c)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(2));

        

        (d)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(3));

        

        (e)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(1));

        

        (f)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(2));

        

        (g)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(3));

        

拓展:

(5)、创建函数 getParentList 查询父节点树

        CREATE FUNCTION getParentList(rootId VARCHAR(10000)) 
        RETURNS VARCHAR(10000) CHARSET utf8  
        BEGIN   
                DECLARE sParentList VARCHAR(10000);   
                DECLARE sParentTemp VARCHAR(10000);   
          
                SET sParentTemp =rootId;   
          
                WHILE sParentTemp IS NOT NULL DO   
                        IF (sParentList IS NOT NULL) THEN   
                                SET sParentList = CONCAT(sParentTemp,',',sParentList);   
                        ELSE   
                                SET sParentList = CONCAT(sParentTemp);   
                        END IF;   
                        SELECT GROUP_CONCAT(
pid) INTO sParentTemp FROM treeList WHERE FIND_IN_SET(id,sParentTemp)>0;   
                END WHILE;   
                RETURN sParentList;   

         END;

(6)、创建函数 getBetweenNodes 查询父、子节点中的节点数

        CREATE FUNCTION getBetweenNodes(startNodeId VARCHAR(100), endNodeId VARCHAR(100)) 
      RETURNS VARCHAR(10000) CHARSET utf8  
      BEGIN  
           DECLARE sBetweenNodes VARCHAR(10000);  
           SELECT GROUP_CONCAT(id) INTO sBetweenNodes FROM treeList 
       WHERE FIND_IN_SET(id, getParentNodes(endNodeId)) 
       AND FIND_IN_SET(pid, getChildNodes(startNodeId));  
           RETURN sBetweenNodes;  
      END;

ORG_CODE RS GZ TJZJ LEADE_ID 60899000 80830000 80830500 80829500 80829000 30001 100 200 300 300 30002 101 201 301 300 30003 102 202 302 300 30004 103 203 303 300 30501 104 204 304 305 30502 105 205 305 305 30503 106 206 306 305 30512 107 207 307 305 29501 108 208 308 295 29504 109 209 309 295 29511 110 210 310 295 29012 111 211 311 290 29013 112 212 312 290 29005 113 213 313 290 在oracle数据库中,我现在有一张公司表,其中表有部门编号、部门人数、工资、团建资金、小组的父级关系,这几个字段(只有小组由有父级关系,可根据部门编码中看出相对应的关系,如:技术部80830000,技术部下的小组a30001、小组b30002、小组c30003,可以根据其父级id对应出部门编码中的‘80830000’中提取300这中间三位数,对应小组的父级id),表中的部门分为3个层级:公司、部门、小组,现在表中只有第3层小组有数据,要求在该表上统计每个部门的总人数和公司的总人数、每个部门的总工资和公司的总工资、每个部门的团建资金和公司总团建资金,其中60899000是公司编号,应该如何去写这段查询SQL语句,不使用cte递归,结果展示为:部门编号、总人数、总工资、总团建资金,要求最后统计的结果展示为:部门编码、总人数、总工资、总团建资金,其中部门编码中有:公司、部门、小组,原有的小组数据逐层汇总到部门、公司,我给的数据中不止一个部门,有好几个部门,不进行无数据部门节点排除,每个部门都有其下的小组,小组中都有数据。(层级关系有问题,公司编码是定死的跟部门编码无丝毫关联,只需要知道60899000是公司编码,需要汇总所有数据;部门编码是80827000,其下小组编码是27001,他们的关系是部门编码的270这三位和小组编码的前三位270,是以这样进行上下关系联系的;部门层级是提取‘80827000’中的‘270’、‘80827500’的‘275’,小组层级的是提取'27001'的前三位‘270’、‘27013’的‘270’、‘27501’的‘275’)
04-01
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值