文档章节

Oracle树形结构的sql语句

Modx
 Modx
发布于 2015/05/08 17:08
字数 3293
阅读 43
收藏 1
点赞 0
评论 0
 

一、基本概念:

1  树结构的描述

树结构的数据存放在表中,数据之间的层次联系即父子联系,通过表中的列与列间的联系来描述, EMP表中的EMPNOMGREMPNO示意该雇员的编号,MGR示意领导该雇员的人的编号,即子节点的MGR值等于父节点的EMPNO值。在表中的每一行中都有一个示意父节点的MGR(除根节点外),通过每个节点的父节点,就能够确定整个树结构。

SELECT命令中运用 CONNECT BY START WITH 子句能够查询表中的树型结构联系。其命令格式如下:

SELECT 。。。

CONNECT BY {PRIOR 列名1=列名2列名1=PRIOR 列名2}

[START WITH]

其中:CONNECT BY子句表明每行数据将是按层次顺序检索,并规定将表中的数据连入树型结构的联系中。PRIOR运算符必须放置在连接联系的两列中某一个的前面。对于节点间的父子联系,PRIOR运算符在一侧示意父节点,在另一侧示意子节点,从而确定查找树结构是的顺序是自顶向下仍旧自底向上。在连接联系中,除了能够使用列名外,还准许运用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则示意所有满足查询条件的行作为根节点。START WITH:不但能够指定一个根节点,还能够指定多个根节点。

2 关于PRIOR

运算符PRIOR被放置于等号前后的位置,决定着查询时的检索顺序。PRIOR被置于CONNECT BY子句中等号的前面时,则强制从根节点到叶节点的顺序检索,即由父节点向子节点方向议决树结构,咱们称之为自顶向下的方式。如:

CONNECT BY PRIOR EMPNO=MGR

PIROR运算符被置于CONNECT BY 子句中等号的后面时,则强制从叶节点到根节点的顺序检索,即由子节点向父节点方向议决树结构,咱们称之为自底向上的方式。比方:

CONNECT BY EMPNO=PRIOR MGR

在这种方式中也应指定一个开始的节点。

3 定义查找起始节点

在自顶向下查询树结构时,不但能够从根节点开始,还能够定义任何节点为起始节点,以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

4.运用 LEVEL

在具有树结构的表中,每一行数据都是树结构中的一个节点,由于节点所处的层次位置不一样,所以每行记载都能够有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始,该起始根节点的层号始终为1,根节点的子节点为2 依此类推。

5.节点和分支的裁剪

在对树结构执行查询时,能够去掉表中的某些行,也能够剪掉树中的一个分支,运用 WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。

6.排序显示

象在其它查询中一样,在树结构查询中也能够运用 ORDER BY 子句,改动查询结果的显示顺序,而不必按照遍历树结构的顺序。但需注意与ORDER SIBLINGS BY的区别应用。

 

二、实验与分析

1.先来看下基本用法:

有一个EMP:

EMPNO   NAME       MGR

10              JACK                   11

11              MARY       12

12             LUCY       15

13             DAN       15

14              TIM        11

15             DORA

 

这个表形成了一个树结构, MGR表示该用户的领导,

假如我们SQL这样写:

SELECT * FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH NAME='LUCY';

这句SQL意思就是查出LUCY的所有手下 

注:SELECT * FROM EMP CONNECT BY MGR =PRIOR EMPNO START WITH NAME='LUCY';

和上面意思完全一样。

查询结果如下:

EMPNO   NAME       MGR

12             LUCY       15

11             MARY       12

10             JACK        11

 

假如SQL我们这样写:

SELECT * FROM EMP CONNECT BY PRIOR MGR = EMPNO START WITH NAME='LUCY'

这样的话, PRIOR所在的MGR就是EMPNO的子列,即EMPNOMGR的父标识,也就是说我们要完全把关系颠倒过来看,这句SQL里把MGR作为每个员工的工号,EMPNO是每个员工领导的工号,树结构查询总是遍历PRIOR后的子标识。

所以查询结果是:

EMPNO   NAME       MGR

12             LUCY       15

15             DORA

(这里MGR是员工号,EMPNO是领导工号,所以LUCY是领导,工号是15,遍历其所有员工,员工的领导工号是15的员工,所以DORA是其员工)

注意这里很有意思,这种方法遍历出来的结果正好是按照正常理解(EMPNO是员工号,MGR是领导工号),对查询用户的所有领导的遍历(DORA按照表定义的时候是LUCY的领导)。

PRIOR如果放在子字段上,则通过START WITH后的字段,遍历所有该字段的下属记录

PRIOR如果放在父子段上,则通过START WITH后的字段,遍历所有该字段的上属记录

START WITH 是指从哪条记录开始遍历,如果不写则会依次遍历所有记录的下属(或者上属)

如果要限制不想显示一个记录的所有下属(上属),要在CONNECT BY 后限制,如我们要显示LUCY的所有下属,但不想显示MARY 和其所有下属,则

SELECT * FROM EMP CONNECT BY PRIOR EMPNO = MGR AND NAME<>'MARY' START WITH NAME='LUCY'

结果:

EMPNO   NAME       MGR

12              LUCY       15

但如果我们只是不想显示MARY,但MARY的下属我们还想显示出来,则在WHERE 后限制

SELECT * FROM EMP WHERE NAME<>'MARY' CONNECT BY PRIOR EMPNO = MGR START WITH NAME='LUCY'

结果:

EMPNO   NAME       MGR

12             LUCY       15

10              JACK        11

 

2.我们再来关注一下排序(ORDER BY & ORDER SIBLINGS BY)和伪列(LEVEL)的应用:

有一个DEPT表:

DEPT_ID  PARENT_ID      DEPT_NAME    AMOUNT

1                               1                          2

2       1                          1-2         15

3       1                          1-3         8

4       2                          1-2-4       10

5       2                          1-2-5       9

6       3                          1-3-6       17

7       3                          1-3-7       5

8       3                          1-3-8       6

我们来看这样两个需求:

a.部门1及其所有下级部门,且所有部门按照人数升序排列。

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM DEPT

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER BY AMOUNT ASC

结果是这样的:

DEPT_ID  PARENT_ID      DEPT_NAME    AMOUNT

1                   1           2

7       3                          1-3-7        5

8       3                          1-3-8        6

3       1                          1-3          8

5       2                          1-2-5        9

4       2                          1-2-4        10

2       1                          1-2          15

6       3                          1-3-6        17

排序在最后被执行,所以DEPT_ID完全被打乱了,而且层级关系也打乱了。

b.部门1及其所有下级部门,每个部门的下一级部门之间,按照人数升序排列。(有同一上级的那些部门)

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM DEPT

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER SIBLINGS BY AMOUNT ASC

结果是这样的:

DEPT_ID  PARENT_ID      DEPT_NAME    AMOUNT

1                    1           2

3       1                          1-3         8

7       3                          1-3-7        5

8       3                          1-3-8        6

6       3                          1-3-6        17

2       1                          1-2         15

5       2                          1-2-5        9

4       2                          1-2-4       10

同属部门间排序,输出结果可见,部门32作为一组进行排序,部门786为一组,54为一组。

 

3.再来看这样一个案例:

给出ABC三个元素,求出这三个元素对应的所有非空子集(含本集),且是顺序无关的。问用SQL语句该如何实现?

首先,我们可以确认的是,这三个元素所组成的非空子集为:

A)(A,B)(A,B,C)(A,C)(B)(B,C)(C)这么七个。

这个问题看起来很简单,实现起来还是要动一番脑筋的。

建表:CREATE TABLE TMP AS (SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM < 4)

我的第一次尝试是这样的:

SELECT SYS_CONNECT_BY_PATH(N, ',')  T, LEVEL N

FROM TMP

CONNECT BY LEVEL < 4

运行结果为:

         T                N

1       ,1               1

2       ,1,1           2

3       ,1,1,1        3

4       ,1,1,2        3

5       ,1,1,3        3

6       ,1,2           2

7       ,1,2,1        3

8       ,1,2,2        3

9       ,1,2,3        3

10     ,1,3           2

11     ,1,3,1        3

12     ,1,3,2        3

13     ,1,3,3        3

14     ,2               1

15     ,2,1           2

16     ,2,1,1        3

17     ,2,1,2        3

18     ,2,1,3        3

19     ,2,2           2

20     ,2,2,1        3

21     ,2,2,2        3

22     ,2,2,3        3

23     ,2,3           2

24     ,2,3,1        3

25     ,2,3,2        3

26     ,2,3,3        3

27     ,3               1

28     ,3,1           2

29     ,3,1,1        3

30     ,3,1,2        3

31     ,3,1,3        3

32     ,3,2           2

33     ,3,2,1        3

34     ,3,2,2        3

35     ,3,2,3        3

36     ,3,3           2

37     ,3,3,1        3

38     ,3,3,2        3

39     ,3,3,3        3

出来的是不排除重复项的39条记录,如何去除这些多余的元素成了我后面烦恼的事情。后来在论坛中其他高手的跟帖中找到了一个很完美的解决方案,真是简单而实用:

 SELECT SYS_CONNECT_BY_PATH(N, ',') T, LEVEL N

 FROM TMP

CONNECT BY N > PRIOR N

运行结果为:

         T                N

1       ,1               1

2       ,1,2           2

3       ,1,2,3        3

4       ,1,3           2

5       ,2               1

6       ,2,3           2

7       ,3               1

这个方案很好的给出了我们所要的答案。它的关键在于connect n > prior n的使用。上面的语句可以跟前面的connect by level <= N结合起来理解。如果没有指定n > prior n,则集合中所有的元素都会成为集合中任意一个元素的叶子。指定了该遍历方向,则集合中所有大于任意一个元素(A)的元素都会成为该元素(A)对应的叶子。

进一步,如果要求出补集该如何做呢?

如,对于集合123,有子集12,则它的补集即为:3。同样论坛上高手给出了一个很巧妙的方法:

CREATE TABLE A AS

(

SELECT T,

       N,

       ROW_NUMBER() OVER(PARTITION BY N ORDER BY T) S,

       COUNT(1) OVER(PARTITION BY N) + 1 C

  FROM (SELECT SYS_CONNECT_BY_PATH(N, ',') T, LEVEL N

          FROM (SELECT ROWNUM N FROM DUAL CONNECT BY ROWNUM < 4)

        CONNECT BY N > PRIOR N)

)

SELECT LTRIM(A.T, ','), LTRIM(B.T, ',')

  FROM A, A B

 WHERE A.N + B.N = 4 - 1

   AND A.S + B.S = B.C

 

三、一些心得总结

1.子句的语法书写顺序。

SELECT -> FROM -> WHERE -> START WITH -> CONNECT BY -> ORDER BY

  WHERE写在CONNECT BY后面就不行,报错。

2.子句的执行顺序

  FROM -> START WITH -> CONNECT BY -> WHERE -> SELECT -> ORDER BY

  执行顺序WHERECONNECT BY之后。

  可是书写SQL语句的时候,却只能写前面,注意理解。

3.如何理解和记忆“CONNECT BY PRIOR MGR = EMPNO ”的含义呢?

  现在看这个例子似乎很直观,但是今后实际应用时,条件变化后,如何推断查询结果呢?

  这里我自己总结一种方法,前提是要理解SQL语句执行时,是一条一条记录来处理的。

  每条满足START WITH语句条件的记录被依次取出,暂且把每次被取出处理的记录,称为当前记录。

  “PRIOR MGR”表明从当前记录得到MGR

  然后" = EMPNO "说明找到表中所有EMPNO等于当前记录MGR的记录,也就是找当前记录MGR所指向的记录。

  因为MGR的取值含义是上级节点,所以说明是向树的根节点方向的搜索。(我的上级是谁?)

  反之,如果是“CONNECT BY MGR = PRIOR EMPNO”,“PRIOR”在EMPNO一边,就是找所有MGR等于当前记录EMPNO的记录,是向树的叶子方向的搜索。(谁的上级是我?)

  找到结果记录集以后,从第一条记录开始递归处理,依此类推。

4.前序遍历

  由于是递归处理,树的根节点向叶子节点递归查询时,查询节点的顺序是按照树的前序遍历进行的。

5.排序

  实验2说明了两种排序的区别。

  IN A HIERARCHICAL QUERY, DO NOT SPECIFY EITHER ORDER BY OR GROUP BY, AS THEY WILL DESTROY THE HIERARCHICAL ORDER OF THE CONNECT BY RESULTS. IF YOU WANT TO ORDER ROWS OF SIBLINGS OF THE SAME PARENT, THEN USE THE ORDER SIBLINGS BY CLAUSE. SEE ORDER_BY_CLAUSE.

6.伪列LEVEL

  只能随CONNECT BY子句一起使用,是一个整数,代表递归的层次深度。也就是节点在树中所处深度。

  根节点时等于1,根节点的叶子节点的深度等于2,依此类推。

实验2LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID 正是利用了LEVEL来为每个层级的字段提供不同的缩进。

7SYS_CONNECT_BY_PATH(COLUMN,CHAR)

用于返回从根到节点的列值路径。

 





© 著作权归作者所有

共有 人打赏支持
Modx
粉丝 0
博文 8
码字总数 23095
作品 0
黄冈
程序员
MySQL · 特性分析 · common table expression

common table expression Common table expression简称CTE,由SQL:1999标准引入, 目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g......

阿里云RDS-数据库内核组
2017/04/06
0
0
oracle 层次查询语句

Oracle中层次查询语法: select ... [level | connect_by_root |connect_by_isleaf |connect_by_iscycle ] from table_name [where] connect by { prior col1=col2 | col1=prior col2 } [sta......

天黑顺路
2017/10/24
0
0
sql递归查询替代方法

自己在做项目时遇到个问题,求助大家,谢谢。数据库用的是oracle,test表结构如下,id,pid,name,rwno(排列序号),想做个树形菜单, 1)select t.*, level, connect_by_isleaf from tes...

小山羊
2012/11/06
581
1
Hibernate加载树形结构的数据怎么用上二级缓存?

经常有树形结构的数据保存在一张二维表中,例如组织架构,多级菜单等。一般是在一张表中使用ID,PID作关联。我项目中的是这样一个树形菜单: @Entity@Cache(usage = CacheConcurrencyStrategy...

喜之郎
2012/09/29
1K
4
PHP+Mysql树型结构(无限分类)数据库设计的2种方式实例

我们经常需要在关系型数据库中保存一些树状结构数据,比如分类、菜单、论坛帖子树状回复等。常用的方法有两种: 1. 领接表的方式; 2. 预排序遍历树方式; 假设树状结构如下图: 领接表方式 ...

我心中有猛狗
2016/10/29
43
0
树形结构的数据库表Schema设计

程序设计过程中,我们常常用树形结构来表征某些数据的关联关系,如企业上下级部门、栏目结构、商品分类等等,通常而言,这些树状结构需要借助于数据库完成持久化。然而目前的各种基于关系的数...

Sub
2013/03/25
0
2
Oracle中start with...connect by子句的用法

Oracle中start with...connect by子句的用法Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的,其基本语法是:select … from tablenamestart with 条件1conn...

什么是程序员
2016/11/11
5
0
oracle 层次查询

1 定义: 层次查询使用树的遍历,走遍含树形结构的数据集合,来获取树的层次关系报表的方法 树形结构的父子关系,你可以控制: ① 遍历树的方向,是自上而下,还是自下而上 ② 确定层次的开始...

长平狐
2012/09/19
130
0
Oracle中通过一条sql遍历树形结构

Oracle 针对树形结构的数据,输入一个根节点,查询出他的所有子节点或者父节点, 可以通过 Start with。(根节点条件)Connect by prior (子节点条件);其中用PRIOR表示上一条记录,比如 ...

沉默的子明
2013/07/10
0
0
Oracle开发技能提升之层次查询全面解析

作者介绍 丁俊,DBAplus社群联合发起人,性能优化专家,Oracle ACEA。电子工业出版社终身荣誉作者,《剑破冰山-Oracle开发艺术》副主编。8年电信行业从业经验,从事系统开发与维护、业务架构...

丁俊
2016/09/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

JavaEE——JavaScript

声明:本栏目所使用的素材都是凯哥学堂VIP学员所写,学员有权匿名,对文章有最终解释权;凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。 JavaScript 内置对象 String对象方法: date对象...

凯哥学堂
7分钟前
0
0
Git 远程代码回滚master

方式一(推荐): git revert commit-id 方式二(不推荐):不推荐原因:推送到线上后,其它开发人员需要重新clone git reset --hard commit-id git push origin HEAD --force...

浮躁的码农
7分钟前
0
0
Elasticesearch学习(7)—— ES查询与Java API的对应关系

1、普通查询 类型 ES查询语句 Java查询实现 结果 查询格式 { "query": { "bool": { "must": [], "must_not": [], "should": [], "filter": [] } }, "from": 0, "size": 10, "sort": [] } Que......

叶枫啦啦
8分钟前
4
0
getElementsByClassName()与getElementById()区别

1.document.getElementsByClassName() 返回的是数组 使用:document.getElementsByClassName("className")[0].innerText='具体内容' 2.document.getElementById() 返回的是单个元素 使用:d......

botkenni
18分钟前
0
0
MyBatis入门

一、安装 <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>x.x.x</version></dependency> 二、从 XML 中构建 SqlSessionFactory String r......

一个yuanbeth
19分钟前
0
0
聊聊spring cloud的LoadBalancerAutoConfiguration

序 本文主要研究一下spring cloud的LoadBalancerAutoConfiguration RibbonAutoConfiguration spring-cloud-netflix-ribbon-2.0.0.RC2-sources.jar!/org/springframework/cloud/netflix/ribb......

go4it
22分钟前
0
0
【转】使用Lombok来优雅的编码

前言 Lombok 是一种 Java™ 实用工具,可用来帮助开发人员消除 Java 的冗长,尤其是对于简单的 Java 对象(POJO)。它通过注解实现这一目的。 正文 添加依赖 在 pom.xml 文件中添加相关依赖:...

HAVENT
24分钟前
0
0
Dubbo 源码解读 —— 可支持序列化及自定义扩展

一、概述 从源码中,我们可以看出来。目前,Dubbo 内部提供了 5 种序列化的方式,分别为 fastjson、Hessian2、Kryo、fst 及 Java原生支持的方式 。 针对不同的序列化方式,对比内容如下: 名...

Ryan-瑞恩
32分钟前
0
0
MySQL内存设置—— MySQL server has gone away

set global max_allowed_packet=268435456

一梦心草
41分钟前
0
0
推导式

列表、集合和字典推导式 列表推导式是Python最受喜爱的特性之一。它允许用户方便的从一个集合过滤元素,形成列表,在传递参数的过程中还可以修改元素。形式如下: [expr for val in collect...

火力全開
46分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部