精华内容
下载资源
问答
  • oracle 树形查询

    万次阅读 2018-09-20 10:49:30
    各位朋友,我已开通微信公共号:小程在线 ...--1.1 简单的树形查询 SELECT empno AS 员工编码, ename AS 姓名, mgr AS 主管, (PRIOR ename) AS 主管姓名 FROM emp START WITH empno=7566 cONNECT ...

    关注微信公共号:小程在线

    关注CSDN博客:程志伟的博客

     

    --1.1 简单的树形查询
    SELECT empno AS 员工编码,
           ename AS 姓名,
           mgr   AS 主管,
           (PRIOR ename) AS 主管姓名
      FROM emp
      START WITH empno=7566
      cONNECT BY (PRIOR empno) = mgr;

    --1.2 根节点,分支节点,叶节点

    level:返回所在的等级;

    connect_by_isleaf:如果当前节点下没有其他节点返回1,其他返回0。
    SELECT LPAD('-',(LEVEL-1)*2,' -')|| empno AS 员工编码,
           ename AS 姓名,
           mgr   AS 主管编码,
           LEVEL AS 级别,
           DECODE(LEVEL,1,1) AS 根节点,
           DECODE(connect_by_isleaf,1,1) AS 叶子节点,
           CASE WHEN (connect_by_isleaf=0 AND LEVEL>1) THEN 1 END AS 分支节点
      FROM emp
     START WITH empno=7566
     CONNECT BY (PRIOR empno)=mgr;

    --1.3 sys_connect_by_path 

    sys_connect_by_path :为了方便的显示其路径
    SELECT empno AS 员工编码,
           ename AS 姓名,
           mgr   AS 主管,
           sys_connect_by_path(ename,' , ') AS enames
     FROM emp
     START WITH empno=7566
     CONNECT BY (PRIOR empno) = mgr;

    --1.4 树形查询中的排序
    SELECT LPAD('-',(LEVEL-1)*2,' -')|| empno AS 员工编码,
           ename AS 姓名,
           mgr   AS 主管编码
      FROM emp
     START WITH empno=7566
     CONNECT BY (PRIOR empno) = mgr
     ORDER SIBLINGS BY empno DESC; 

     

    展开全文
  • 前台结构依据个人的权限登录变化 全部我查询要依据 的ID 查询以下全部的子节点 以及本节点的信息 select * from table start with id = #{id} connect by prior id = pid 这里的id就是 父节点的ID ...

    前台树结构依据个人的权限登录变化 全部我查询要依据 树的ID 查询以下全部的子节点 以及本节点的信息

     select * from table start with id = #{id} connect by prior id = pid

    这里的id就是 父节点的ID 


    转载于:https://www.cnblogs.com/brucemengbm/p/6900756.html

    展开全文
  • 1. 简单的树形查询 表结构和数据如下: --建表语句 create table DEPT ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ); alter table DEPT add constraint PK_DEPT primary key (DEPTNO); ...

    1. 简单的树形查询

    表结构和数据如下:

    --建表语句
    create table DEPT
    (
    DEPTNO NUMBER(2) not null,
    DNAME VARCHAR2(14),
    LOC    VARCHAR2(13)
    );
    alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
    create table EMP
    (
    EMPNO    NUMBER(4) not null,
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2)
    );
    alter table EMP add constraint PK_EMP primary key (EMPNO);
    comment on table emp  is  '员工表';
    comment on column emp.empno is  '员工号';
    comment on column emp.ename is  '员工姓名';
    comment on column emp.job is  '职务';
    comment on column emp.mgr is  '上级编号';
    comment on column emp.hiredate is  '受雇日期';
    comment on column emp.sal is  '薪金';
    comment on column emp.comm is  '奖金';
    comment on column emp.deptno is  '部门编号';
    comment on table dept  is  '部门表';
    comment on column dept.deptno is  '部门编号';
    comment on column dept.dname is  '部门名称';
    comment on column dept.loc is  '部门地址';
    --初始化数据
    
    insert into DEPT (DEPTNO, DNAME, LOC)
    values (10, 'ACCOUNTING', 'NEW YORK');
    insert into DEPT (DEPTNO, DNAME, LOC)
    values (20, 'RESEARCH', 'DALLAS');
    insert into DEPT (DEPTNO, DNAME, LOC)
    values (30, 'SALES', 'CHICAGO');
    insert into DEPT (DEPTNO, DNAME, LOC)
    values (40, 'OPERATIONS', 'BOSTON');
    commit;
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
    commit;
    
    

    我们经常会用表来保存上下级关系,如地区表,员工表,组织机构表等,为了按上下级关系递归查询这些数据,就需要用到树形查询,例如:

    select level,empno,ename,mgr,(prior ename) as mgr_name 
    from emp
    start with mgr is null
    connect by (prior empno)=mgr;
    

    查询结果如下:
    在这里插入图片描述
    用图来表示如下:

    KING
    BLAKE
    CLARK
    JONES
    TURNER
    MARTIN
    JAMES
    ALLEN
    WARD
    MILLER
    SCOTT
    FORD
    SMITH
    ADAMS

    这个语句已“mgr is null”为起点向下循环用“(prior empno)=mgr”递归查询。
    Level是一个伪列,表示节点在树中的层级,prior关键字可以返回上级的信息。

    2. 根节点、分支节点、叶子节点

    connect_by_isleaf是一个伪列,如果当前节点下没有其它的节点,则connect_by_isleaf返回1,否则返回0.

    select lpad(' ',(level-1)*2,' ') || empno as empno,
           ename,mgr,level,
           decode(level,1,1) as root_node,--根节点
           case when
               (connect_by_isleaf = 0 and level > 1) then
               1
           end as branch_node, --分支节点
           decode(connect_by_isleaf,1,1) as leaf_node--叶子节点
    from emp
    start with empno='7566'
    connect by (prior empno) = mgr;
    
    --sys_connect_by_path展示全路径
    select empno,ename,mgr,sys_connect_by_path(ename,',') as connect_path
    from emp
    start with empno='7566'
    connect by (prior empno)=mgr;
    
    --用sys_connect_by_path合并字符串
    with x1 as 
    /*1. 分组生成序号 rn*/
         (select deptno,ename,row_number() over(partition by deptno order by ename)as rn
         from emp)
    /*2. 用sys_connect_by_path合并字符串*/
    select deptno,sys_connect_by_path(ename,',') as emps
    from x1
    where connect_by_isleaf=1
    start with rn=1
    connect by (prior deptno)=deptno
            and (prior rn)=rn-1; 
    --用listagg的方式(不需要生成rn)            
    with x1 as 
         (select deptno,ename
         from emp)select deptno,listagg(ename,',') within group(order by ename) as enames from x1 group by deptno;
    

    3.树形查询中的排序

    一般排序的方式
    select lpad(’ ‘,(level-1)*2,’ ') || empno as empno,ename,mgr
    from emp
    start with empno=‘7566’
    connect by (prior empno)=mgr;
    order by empno desc;
    这种排序方式无法再看清上下级关系,失去了树形查询的意义,我们应该使用树形查询的专用关键字“SIBLINGS”

    树形查询中的排序

    select lpad(' ',(level-1)*2,' ') || empno as empno,ename,mgr
    from emp
    start with empno='7566'
    connect by (prior empno)=mgr
    order SIBLINGS by emp.empno desc;
    

    可以看到,这个语句只对同一分支(7566)下的(7902,,7788)进行排序,而没有影响到树形结构。
    需要注意的是SIBLINGS关键字仅对树形查询生效,所以这里要用emp.empno,如果不加emp前缀,则指的是表达式的结果,会报错。

    4.树形查询中的where

    传统where的使用方式

    select empno,mgr,ename,deptno
    from emp
    where deptno='20'
    start with mgr is null
    connect by (prior empno)=mgr;
    

    这个结果明显不对,因为部门20不存在mgr为空的数据,那么也就不该返回数据。上面查询可等价改写为:

    select * from (select empno,mgr,ename,deptno
    from emp
    start with mgr is null
    connect by (prior empno)=mgr)
    where deptno='20';
    

    也就是说,上面的语句实际是先上树,再过滤。

    树形查询中的where
    而只查询部门20的数据,且部门20的起始条件为empno=‘7566’,所以应该这样写:

    select empno,mgr,ename,deptno
    from (select * from emp where deptno='20')
    start with empno ='7566'
    connect by (prior empno)=mgr;
    

    本文内容来自《Oracle查询优化改写技巧与案例2.0》/有教无类,落落著。北京:电子工业出版社,2018.6

    展开全文
  • Oracle查询优化改写技巧与案例

    热门讨论 2016-03-18 19:51:11
    第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
  • 树查询 使用树查询的前提条件是: 在一条记录中记录了当前节点的ID和这个节点的父ID。 注意:一旦数据中出现了循环记录,如两个节点互为对方的父结点,系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT ...

    树查询

    使用树查询的前提条件是: 在一条记录中记录了当前节点的ID和这个节点的父ID

    注意:一旦数据中出现了循环记录,如两个节点互为对方的父结点,系统就会报 ORA-01436错误(ORA-01436: 用户数据中的CONNECT BY 循环)


    第一步:创建表

    create table MENU
    (
      MENU_ID   NUMBER not null,
      PARENT_ID NUMBER,
      MENU_NAME NVARCHAR2(20)
    );

    第二步:插入数据

    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (1, null,'AAAA');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (2, 1, 'BBBB');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (3, 1, 'CCCC');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (4, 1, 'DDDD');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (5, 2, 'EEEE');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (6, 2, 'FFFF');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (7, 2, 'GGGG');
    insert into MENU (MENU_ID, PARENT_ID, MENU_NAME) values (8, 3, 'HHHH');
    commit;


    第三步:查询

    select menu_id,rpad(' ',(level-1)*4)||menu_name from menu
    connect by parent_id = prior menu_id
    start with parent_id is null

    显示出树的级别查询

    select menu_id,rpad(' ',(level-1)*4)||menu_name,level from menu 
     connect by parent_id = prior menu_id 
     start with parent_id is null;



    也可以这样写:

    select role_id,parent_grade_id,level from audit_role 
     where level > 1
     connect by prior role_id = parent_grade_id    
     start with role_id=1   
     order by level;


    展开全文
  • 本书不讲具体语法,只是以案例的形式介绍各种...第 5~12 章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数 的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。
  • 第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
  • 第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。, 如果您是开发...
  • 以下是一系列针对结构的更深层次的查询,这里的查询不一定是最优的查询方式,或许只是其中的一种实现而已。 3.查询与一个节点同级的节点(族兄弟)。 如果在表中设置了级别的字段,那么在做这类查询时会很轻松,...
  • Oracle树形查询

    2013-05-08 13:49:51
     Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法,我们可以将一个表结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以
  •  Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法,我们可以将一个表结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树...
  • 第5~12章是提高部分,讲解了正则表达式、分析函数、树形查询及汇总函数的用法。这部分知识常用于对一些复杂需求的实现及优化改写。最后两章介绍日常的优化改写案例。这部分是前面所学知识的扩展应用。 如果您是开发...
  • 简单语法介绍一、树型表结构: 节点ID 上级ID 节点名称 二、公式: select 节点ID,节点名称,level from 表 connect by prior 节点...在 Oracle Database 11g 第 2 版中查询层次结构数据的快速入门作者:Tim Hartley20
  • 图例:假设已经知道安徽省的id为 1218 ,我想查询安徽省下面所有相关信息 ----1218 安徽省----1220 合肥市----1226 1224 美容店oracle sql 这样写select * from fht_enterprise start with id = 1218 ...
  • oracle树查询

    2016-06-24 16:54:14
    Oracle树查询的最重要的就是select...start with... connect by...prior 语法了。依托于该语法,我们可以将一个表结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询...
  • Oracle树查询及函数

    万次阅读 2016-06-16 10:04:24
    Oracle树查询的最重要的就是select...start with... connect by...prior语法了。依托于该语法,我们可以将一个表结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询...
  • 本文是总结Oracle查询优化方法与改写技巧的第四篇文章,接着第三篇文章,继续。。。 二、优化技巧 【1】将结果集反向转置为一列 案例:将以下结果集反向转置为一列进行展示,并且每个员工信息之后空一行。 ...
  • 在项目应用中,有些表的数据呈现出树型结构,比如部门表,菜单表,如果我们按照常规的方法查询出来,可能不太直观,这时需要借助LPAD函数以及LEVEL伪列来分层查询 select v.id, LPAD(' ', 2 * LEVEL - 1, ' ') ||...
  • oracle 数据库提供递归查询语句: start with connect by prior: create table provincial_city ( parentid varchar2(10), subid varchar2(10)); insert into provincial_city values ( '陕西省', '西安...
  • (一)递归 一个过程或函数在其定义或说明中有直接或间接调用自身的一种方法。 菲波那切数列就是利用递归定义的: F0 = 0F1 = 1Fn = F(n – 1 )+ F(n – 2) ...Oracle Database 11g 第 2 版通过子查询...
  • 创建结构表 --部门目录结构表 CREATE TABLE BAI_ORG (  BAI_ORG_ID NUMBER PRIMARY KEY , --部门ID  BAI_ORG_NAME VARCHAR2(225), --部门名字  BAI_ORG_P_ID NUMBER , --部门父级级部门....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,832
精华内容 732
关键字:

oracle树形查询技巧