Oracle高级查询之CONNECT BY

为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。

Oracle中的select语句可以用start with ... connect by prior ...子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:

select ... from 
where <过滤条件,用于对返回的所有记录进行过滤>
start with <根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树>
connect by [prior] <连接条件,其中用prior表示上一条记录,比如:connect by prior t.id = t.parent_id就是说上一条记录的id 是本条记录的parent_id,即本记录的父亲是上一条记录> 
下面我们直接来看实例,查询‘KING‘的所有下属雇员。SQL语句如下:

[sql] view plain copy

  1. select *
  2. from scott.emp e
  3. start with e.ename = ‘KING‘
  4. connect by prior e.empno = e.mgr;

我们再来看另外一个实例,反过来查询‘SMITH‘的所有上司。SQL语句如下:

[sql] view plain copy

  1. select *
  2. from scott.emp e
  3. start with e.ename = ‘SMITH‘
  4. connect by e.empno = prior e.mgr;

通过上面的两个实例,估计大家应该理解的差不多了,接下来介绍connect by的几个固定搭档。

1、sys_connect_by_path函数
语法:sys_connect_by_path(列名, ‘分隔符‘)。
作用:从start with的地方开始遍历,将遍历到的路径根据函数中的分隔符,组成一个新的字符串。

[sql] view plain copy

  1. select sys_connect_by_path(ename, ‘/‘) ename_tree
  2. from scott.emp
  3. start with ename = ‘KING‘
  4. connect by mgr = prior empno;

插个题外话,介绍sys_connect_by_path函数使用的一个小技巧,把查询行转换成列,把表emp的所有列名以‘|‘分隔开输出(提示:大家可以把下面的语句拆开来逐个分析),SQL语句如下:

[sql] view plain copy

  1. select max(ltrim(sys_connect_by_path(column_name, ‘|‘), ‘|‘)) column_names
  2. from (select column_name, rownum rnum
  3. from user_tab_columns
  4. where table_name = ‘EMP‘)
  5. start with rnum = 1
  6. connect by rnum = rownum;

2、level:在结构化查询结果中,每一行都是结构中的一个节点,level表示该节点在结构中的层次,根节点为1,根节点的子节点为2,以此类推。
下面SQL语句很直观的展示效果:

[sql] view plain copy

  1. select ename, sys_connect_by_path(ename, ‘/‘) ename_tree, level
  2. from scott.emp
  3. start with ename = ‘KING‘
  4. connect by mgr = prior empno;

3、connect_by_root:用在列名之前,返回当前节点的根节点对应列的值。connect_by_isleaf:返回当前节点是否为叶子节点,“是”返回1,“否”返回0。
下面SQL语句很直观的展示效果:

[sql] view plain copy

  1. select sys_connect_by_path(ename, ‘/‘) ename_tree,
  2. connect_by_root ename as root,
  3. connect_by_isleaf as isleaf
  4. from scott.emp e
  5. start with e.ename = ‘KING‘
  6. connect by prior e.empno = e.mgr;

http://blog.csdn.net/feier7501/article/details/21811319

转自:http://blog.csdn.net/feier7501/article/details/21815691

创建表,初始化数据;

[sql] view plain copy

  1. CREATE TABLE TB_COMPANY
  2. (
  3. COMPANY_ID INTEGER PRIMARY KEY,
  4. COMPANY VARCHAR2(256),
  5. UP_COMPANYID INTEGER
  6. );
  7. INSERT INTO TB_COMPANY VALUES (0, ‘总公司‘, NULL);
  8. INSERT INTO TB_COMPANY VALUES (1, ‘北京分公司‘, 0);
  9. INSERT INTO TB_COMPANY VALUES (2, ‘上海分公司‘, 0);
  10. INSERT INTO TB_COMPANY VALUES (3, ‘海淀区分部‘, 1);
  11. INSERT INTO TB_COMPANY VALUES (4, ‘东城区分部‘, 1);
  12. INSERT INTO TB_COMPANY VALUES (5, ‘黄埔区分部‘, 2);
  13. INSERT INTO TB_COMPANY VALUES (6, ‘静安区分部‘, 2);
  14. COMMIT;

全部数据:

递归查询SQL:

[sql] view plain copy

  1. SELECT (RPAD(‘ ‘, 2*(LEVEL-1), ‘-‘ ) || COMPANY) COMPANY_NAME, CONNECT_BY_ROOT COMPANY, CONNECT_BY_ISLEAF, LEVEL , SYS_CONNECT_BY_PATH(COMPANY, ‘/‘)
  2. FROM TB_COMPANY
  3. START WITH UP_COMPANYID IS NULL
  4. CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

结果:

说明:

1. CONNECT_BY_ROOT 返回当前节点的最顶端节点 
2. CONNECT_BY_ISLEAF 判断是否为叶子节点,如果这个节点下面有子节点,则不为叶子节点 
3. LEVEL 伪列表示节点深度 
4. SYS_CONNECT_BY_PATH函数显示详细路径,并用“/”分隔

递归查询SQL:

[sql] view plain copy

  1. SELECT * FROM TB_COMPANY START WITH COMPANY_ID = 1 CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

结果:

时间: 10-10

Oracle高级查询之CONNECT BY的相关文章

oracle高级查询(实例基于scott用户四张表)

oracle高级查询(实例基于scott用户四张表) 分组查询 多表查询 子查询 综合实例 ======================================================================= scott用户的四张表(emp,dept,bonus,salgrade) 没有这四张表的可参考http://blog.csdn.net/love_legain/article/details/54311040进行创建 -----------------------

Oracle 高级查询1 关联查询 分组查询

高级查询 1.关联查询作用:可以跨越多表查询 --查询出员工的名字和他所在部门的的名字 语法:select 列,列,列 from 表1 join 表2on 表1外键=表2主键 2.外联接 左外联[left outer join] 以关联的左边为准,即使右边没有与之匹配的记录,则左边的记录也要 出现在结果集中,右边全部以NULL值显示. 右外联[right outer join] 以关联的右边为准,即使左边没有与之匹配的记录,则右边的记录也要 出现在结果集中,左边全部以NULL值显示. 3分组查询

Oracle高级查询

本章将学习的内容有: 如何使用集合操作符,集合操作符用于将两个或多个查询返回的行组合起来 使用TRANSLATE函数 1.使用集合操作符 操作符 说明 UNION ALL 返回各个查询检索出的所有行,包括重复行 UNION 返回各个查询检索出的所有行,不包括重复行 INTERSECT 返回两个检索锁的共有行 MINUS 返回将第二个查询检索出的行从第一个查询检索的行中去除之后的行 注意:当使用集合操作符的时候,必须牢记这样一条限制:所有查询返回的列数以及列的类型必须匹配,但列名可以不同. 使用I

Oracle 高级查询

Oracle SQL 一些函数用法 以下sql环境都是在 Oracle 11g/scott完成 Group by 与GROUP BY一起使用的关建字 GROUPING,GROUP SET,ROLLUP,CUBE结合这些特性的函数可以实现SQL的明细+分组统计 GROUPING GROUPING 记录是对哪个字段进行统计,其值只能是 0 & 1 Group(column) =0 表示此字段参与了分组统计 =1表示字段未参与分组统计 select deptno,job,sum(sal),groupi

Oracle高级查询之over(partition by...)

为了方便学习和测试,所有的例子都是在Oracle自带用户Scott下建立的. [sql] view plain copy print? 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   N

Oracle高级查询之GROUP BY

对于group by 的方式不再累赘,高级方式如下案例. 参考老师文章:http://blog.csdn.net/fu0208/article/details/7183258,向老师致敬,辛苦了 为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的.所用emp表, 如果没有此表请参考文章:http://blog.csdn.net/xiaokui_wingfly/article/details/43957003中拷贝 现在客户的需求是统计部门中每种工作的工资总额,最后还需

oracle数据库的高级查询方法 多表联合查询

oracle查询 一,简单查询 order by 1,2 select t.sno, t.sname, t.ssex from STUDENT t order by 1,2,3 group by 增强版 SELECT p.toma, p.ptype, SUM(p.lastcou) FROM product p GROUP BY rollup(p.toma, p.ptype) 二,高级查询(多表连接查询) 笛卡尔积的概念: 所谓笛卡尔积,通俗点说就是指包含两个集合中任意取出两个元素构成的组合的集合

Oracle高级函数1------ Oracle 树操作(select…start with…connect by…prior)

oracle树查询的最重要的就是select…start with…connect by…prior语法了.依托于该语法,我们可以将一个表形结构的以树的顺序列出来.在下面列述了oracle中树型查询的常用查询方式以及经常使用的与树查询相关的oracle特性函数等,在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等. 1.准备测试表和测试数据 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

Oracle -&gt;&gt; 层级查询语句(hierarchical query)connect by

Oracle中的Connect By... Start With语句实现了递归查询或者树状查询. Connect By Prior 一方为起始(root)的ID 参考: http://www.360doc.com/content/13/0422/16/11947209_280153192.shtml http://www.oradev.com/connect_by.jsp http://blog.csdn.net/haiross/article/details/17583725#t0 http: