热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

oracle层次查询

1定义:层次查询使用树的遍历,走遍含树形结构的数据集合,来获取树的层次关系报表的方法树形结构的父子关系,你可以控制:①遍历树的方向,是自上而下,还是自下而...SyntaxHighlighter.all();
 
          1 定义:
 
          层次查询使用树的遍历,走遍含树形结构的数据集合,来获取树的层次关系报表的方法
          树形结构的父子关系,你可以控制:
          ① 遍历树的方向,是自上而下,还是自下而上
          ②  确定层次的开始点(root)的位置
          层次查询语句正是从这两个方面来确定的,start with确定开始点,connect by确定遍历的方向  www.2cto.com  
 
          2 语法:

 
          注释:
          ① level是伪列,表示等级
          ② from后面只能是一个表或视图,对于from是视图的,那么这个view不能包含join
          ③ Where条件限制了查询返回的行,但是不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响
          ④ prior是个形容词,可放在任何地方
          ⑤ 彻底剪枝条件应放在connect by;单点剪掉条件应放在where子句。但是,connect by的优先级要高于where,也就是sql引擎先执行connect by
          ⑥ 在start with中表达式可以有子查询,但是connect by中不能有子查询
 
          3 遍历树:
 
          ㈠ Start with子句
          Start with确定将哪行作为root,如果没有start with,则每行都当作root,然后查找其后代,这不是一个真实的查询。Start with后面可以使用子查询或者任何合法的条件表达式
          例子:
[sql] 
select level,id,manager_id,last_name,title from s_emp  
      start with title=(select title from s_emp where manager_id is null)  
      connect by prior id=manager_id;  
 
          ㈡ Connect by子句
          Connect by与prior确定一个层次查询的条件和遍历的方向(prior确定)
          Connect by prior column_1=column_2;
          其中prior表示前一个节点的意思,可以在connect by等号的前后,列之前,也可以放到select中的列之前  www.2cto.com  
          Connect by也可以带多个条件,比如 connect by prior id=manager_id and id>10
 
                        1. )自顶向下遍历:
                        先由根节点,然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下:connect by prior 父key=子key表示自顶向下,等同于connect by 子key=prior 父key.
                        例子:
[sql] 
select level,employee_id,manager_id,last_name,job_id from s_emp  
      start with manager_id=100  
      connect by  employee_id=prior manager_id;  
 
                         2. )自底向上遍历:
                         先由最底层的子节点,遍历一直找到根节点。与上面的相反。Connect by之后不能有子查询,但是可以加其他条件,比如加上and id !=2等。这句话则会截断树枝,如果id=2的这个节点下面有很多子孙后代,则全部截断不显示。
                         例子:
[sql] 
select level,employee_id,manager_id,last_name,job_id from s_emp  
      start with manager_id=100    www.2cto.com  
      connect by prior employee_id=manager_id and employee_id<>120;  
 
          4 使用level和lpad格式化报表:
          Level是层次查询的一个伪列,如果有level,必须有connect by,start with可以没有
          Lpad是在一个string的左边添加一定长度的字符,并且满足中间的参数长度要求,不满足自动添加
          例子:
[sql] 
select level,employee_id,manager_id,lpad(last_name,length(last_name)+(level*4)-4,&#39;_&#39;),job_id from s_emp  
      start with manager_id=100  
      connect by prior employee_id=manager_id and employee_id<>120  
 
          5 修剪branches:
          where子句会将节点删除,但是其后代不会受到影响,connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意,如果是connect by之后加条件正好条件选到根,那么结果和没有加一样
 
          6 实际应用
          1)查询每个等级上节点的数目
[sql] 
  先查看总共有几个等级:  
select count(distinct level)  
 from s_emp  
 start with manager_id is null  
 connect by prior employee_id=manager_id  
  要查看每个等级上有多少个节点,只要按等级分组,并统计节点的数目即可,可以这样写:  
select level,count(last_name)  
 from s_emp  
 start with manager_id is null  
 connect by prior employee_id=manager_id  
 group by level    www.2cto.com  
 
          2)查看等级关系
          比如给定一个具体的员工看是否对某个员工有管理权
[sql] 
select level,a.* from   
s_emp a  
where first_name=&#39;Douglas&#39; --被管理的节点  
start with manager_id is null --开始节点,即:根节点  
connect by prior employee_id=manager_id  
 
           3)删除子树
           比如有这样的需求,现在要裁员,将某个部门的员工包括经理全部裁掉
           将id为2的员工管理的所有员工包括自己删除
[sql] 
delete from s_emp where employee_id in(  
elect employee_id from   
s_emp a  
start with employee_id=2 --从id=2的员工开始查找其子节点,把整棵树删除  
connect by prior employee_id=manager_id)  
 
           4)找出每个部门的经理
[sql] 
select level,a.* from   
 s_emp a    www.2cto.com  
 start with manager_id is null  
 connect by prior employee_id=manager_id and department_id !=prior department_id;--当前行的dept_id不等于前一行的dept_id,即每个子树中选最高等级节点  
 
           5)查询一个组织中最高的几个等级
[sql] 
select level,a.* from   
 s_emp a  
  where level <=2 –查找前两个等级  
 start with manager_id is null  
 connect by prior employee_id=manager_id and department_id !=prior department_id;  
 
       6)合计层次
          有两个需求,一是对一个指定的子树subtree做累加计算salary,一是将每行都作为root节点,然后对属于这个节点的所有子节点累加计算salary。
[sql] 
     第一种很简单,求下sum就可以了,语句:  
select sum(salary) from   
 s_emp a  
 start with id=2—比如从id=2开始  
 connect by prior id=manager_id;  
  
     第2个需求,需要用到第1个,对每个root节点求这个树的累加值,然后内部层次查询的开始节点从外层查询获得。  
select last_name,salary,(  
  select sum(salary) from   
 s_emp    www.2cto.com  
 start with id=a.id –让每个节点都成为root  
 connect by prior id=manager_id) sumsalary  
 from s_emp a;  
 
           7)找出指定层次中的叶子节点
           Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,0表示非叶子节点
[sql] 
select level,id,manager_id,last_name, title from s_emp  
    where connect_by_isleaf=1 –表示查询叶子节点  
      start with  manager_id=2  
      connect by prior id=manager_id;  
 
          7 10g新特性:
 
          ① 使用SIBLINGS关键字排序
             如果使用order by排序会破坏层次,在oracle10g中,增加了siblings关键字的排序
             语法:order  siblings  by
             它会保护层次,并且在每个等级中按expre排序
             例子:
[sql] 
select level,    www.2cto.com  
       employee_id,last_name,manager_id  
       from s_emp  
       start with manager_id is null  
       connect by prior employee_id=manager_id  
       order siblings by last_name;  
 
          ② CONNECT_BY_ROOT
              Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值
              例子:
[sql] 
select connect_by_root last_name root_last_name, connect_by_root employee_id root_id,  
      employee_id,last_name,manager_id  
      from s_emp  
      start with manager_id is null  
      connect by prior employee_id=manager_id  

推荐阅读
  • 本周,我深入研究了 ECharts 插件的使用方法,整体感觉插件操作较为简便,但后台算法较为复杂。此外,我还学习了 MySQL 函数的新应用,进一步提升了数据库操作的灵活性。同时,分享了自己在 Python 书籍外借过程中的体验,总结了一些实用的借阅技巧和心得。 ... [详细]
  • 如何在Android项目中正确导入和配置MySQL数据库驱动 ... [详细]
  • Norton Partition Magic 中 PHP 函数 error_reporting(E_ALL ^ E_NOTICE) 的详细解析与应用
    在 Windows 环境下,通过具体示例分析了 `Norton Partition Magic` 中 `PHP` 函数 `error_reporting(E_ALL ^ E_NOTICE)` 的详细解析与应用。该函数用于控制错误报告级别,例如在从 PHP 4.3.0 升级到 4.3.1 后,程序出现多处错误的原因及解决方法。本文深入探讨了错误报告配置对程序稳定性的影响,并提供了实用的调试技巧。 ... [详细]
  • 本文深入探讨了ASP.NET中ViewState、Cookie和Session三种状态管理技术的区别与应用场景。ViewState主要用于保存页面控件的状态信息,确保在多次往返服务器过程中数据的一致性;Cookie则存储在客户端,适用于保存少量用户偏好设置等非敏感信息;而Session则在服务器端存储数据,适合处理需要跨页面保持的数据。文章详细分析了这三种技术的工作原理及其优缺点,并提供了实际应用中的最佳实践建议。 ... [详细]
  • Python与R语言在功能和应用场景上各有优势。尽管R语言在统计分析和数据可视化方面具有更强的专业性,但Python作为一种通用编程语言,适用于更广泛的领域,包括Web开发、自动化脚本和机器学习等。对于初学者而言,Python的学习曲线更为平缓,上手更加容易。此外,Python拥有庞大的社区支持和丰富的第三方库,使其在实际应用中更具灵活性和扩展性。 ... [详细]
  • SQL Server开发技巧:修改表结构后的视图批量更新方法与实践 ... [详细]
  • SQLSharper 2014 是一款专为 SQL Server Management Studio (SSMS) 设计的功能增强插件,旨在提升 T-SQL 开发者的效率。该插件提供了多种实用工具,包括快速查询数据库对象、详细查看表结构、优化查询结果导出以及自动生成代码等。适用于需要高效管理和开发 SQL 数据库的专业人士。 ... [详细]
  • 如何在Oracle ASM_Diskgroup中重命名现有磁盘
    如何在Oracle ASM_Diskgroup中重命名现有磁盘 ... [详细]
  • 基于Java和JSP的电子医疗记录管理平台
    随着信息技术的快速发展,各类管理系统已在各行各业得到广泛应用。传统的人工管理模式已逐渐无法满足现代需求。本文介绍了一种基于Java和JSP技术开发的电子医疗记录管理平台,旨在提高医疗行业的信息化水平和管理效率。该平台通过整合先进的数据库技术和Web开发框架,实现了医疗记录的高效存储、查询和管理,为医护人员提供了便捷的操作界面和强大的数据支持。 ... [详细]
  • 在数据库管理中,计算字段(也称为计算列)是一种重要的技术手段。计算字段通过在表定义中使用表达式或函数,自动生成并存储计算结果,从而提高查询效率和数据一致性。本文将详细介绍计算字段的创建方法、优化技巧及其在实际应用中的案例,帮助读者更好地理解和运用这一功能。 ... [详细]
  • 在探讨如何高效处理大规模数据报表的分页展示之前,首先需要明确导致报表加载缓慢的主要原因。通常情况下,这主要是由于两个方面:一是查询条件过于宽泛,使得数据库返回的结果集包含数百万甚至更多的记录;二是前端渲染性能不足,无法高效处理大量数据。为了优化这一过程,可以从以下几个方面入手:优化查询条件,减少不必要的数据返回;采用分页查询技术,每次仅加载所需的数据;利用缓存机制,减少对数据库的频繁访问;提升前端渲染效率,使用虚拟滚动等技术提高用户体验。 ... [详细]
  • 最近,我在CentOS 5服务器上成功部署了GForge 5.7 Community Edition。与Advanced Server版本相比,虽然功能略有简化,但仍然能够满足大多数开源项目管理的需求。为了确保数据安全,我开发了一套全自动备份脚本,该脚本能够定期备份GForge的数据和配置文件,并将其存储在远程服务器上,以防止数据丢失。此外,该脚本还具备错误检测和日志记录功能,便于故障排查和维护。 ... [详细]
  • 在CentOS上部署和配置FreeSWITCH
    在CentOS系统上部署和配置FreeSWITCH的过程涉及多个步骤。本文详细介绍了从源代码安装FreeSWITCH的方法,包括必要的依赖项安装、编译和配置过程。此外,还提供了常见的配置选项和故障排除技巧,帮助用户顺利完成部署并确保系统的稳定运行。 ... [详细]
  • Issue with the Reserved Term HOSTS in System Configuration ... [详细]
  • 在开发系统查询搜索功能时,需注意以下几点以提高信息检索效率:首先,在SQL语句中,每个参数占位符“?”后必须紧跟相应的参数赋值,确保参数与赋值一一对应,避免因参数不匹配导致的错误。其次,进行模糊搜索时,若用户输入通配符“%”,可能会导致全表扫描,因此需要对输入的“%”进行特殊处理或限制,以防止不必要的性能开销。此外,建议使用索引优化查询速度,并合理设计搜索逻辑,以提升用户体验。 ... [详细]
author-avatar
苗淑香哈哈_405_408
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有