Oracle 个中档期的顺序查询的概念   层

2.1.通过Connect by 生成序列

Oracle 构造一个月份的天数select to_date('200809','yyyymm')+(rownum-1) s_date from dual connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm') + 1

图片 1

生成1-10的一个序列SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10

图片 2

--Hierarcical Query
SELECT LEVEL LV
     , EMPNO
     , LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
     , MGR
     , CONNECT_BY_ROOT ENAME ROOT
     , SYS_CONNECT_BY_PATH(ENAME,':')
  FROM EMP_RSF
 START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
 ORDER SIBLINGS BY EMPNO;

--RSF 
WITH E( LV, EMPNO, ENAME, MGR, PATH) AS(
        SELECT 1 LV, EMPNO, ENAME, MGR, ':'||ENAME PATH
          FROM EMP_RSF
         WHERE MGR IS NULL
        UNION ALL
        SELECT Y.LV+1, X.EMPNO, X.ENAME, X.MGR, Y.PATH||':'||X.ENAME
          FROM EMP_RSF X, E Y
         WHERE Y.EMPNO = X.MGR)
SEARCH DEPTH FIRST BY EMPNO SET ORDER1
SELECT LV
     , EMPNO
     , LPAD(' ', LV*2-1,' ')||ENAME ENAME
     , MGR
     , NVL(SUBSTR(PATH, 2, INSTR(PATH, ':', 2)-2),ENAME) ROOT
     , PATH
FROM E;

LV  EMPNO  ENAME         MGR   ROOT  PATH
1   7839   KING                KING  :KING
2   7566      JONES      7839  KING  :KING:JONES
3   7788        SCOTT    7566  KING  :KING:JONES:SCOTT
4   7876          ADAMS  7788  KING  :KING:JONES:SCOTT:ADAMS
3   7902        FORD     7566  KING  :KING:JONES:FORD
4   7369         SMITH   7902  KING  :KING:JONES:FORD:SMITH
2   7698      BLAKE      7839  KING  :KING:BLAKE
3   7499        ALLEN    7698  KING  :KING:BLAKE:ALLEN
3   7521        WARD     7698  KING  :KING:BLAKE:WARD
3   7654        MARTIN   7698  KING  :KING:BLAKE:MARTIN
3   7844        TURNER   7698  KING  :KING:BLAKE:TURNER
3   7900        JAMES    7698  KING  :KING:BLAKE:JAMES
2   7782      CLARK      7839  KING  :KING:CLARK
3   7934        MILLER   7782  KING  :KING:CLARK:MILLER

 

1.2、CONNECT BY 示例

示例
1,列出所有部门自上而下的组织结构层次,并按部门编号在同级部门间排序:

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
CONNECT BY PRIOR t.enum_id=t.parent_enum_id
ORDER SIBLINGS BY t.enum_code;

示例 2,列出软件部门(enum_id=1)自上而下的组织结构层次:

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

示例 3,CONNECT_BY_ROOT 和 CONNECT_BY_ISLEAF 伪列及 WHERE
过滤的语法演示:

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name,CONNECT_BY_ROOT t.enum_id,CONNECT_BY_ISLEAF
FROM demo.t_field_enum t
WHERE t.field_code='DEPT' AND t.enum_id!=7
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

示例 4,查找会造成死循环的节点(数据行):

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT' AND CONNECT_BY_ISCYCLE=1
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id;

示例 5,利用 LEVEL 伪列格式化层次查询结果:

SELECT LEVEL lvl,t.enum_id,t.parent_enum_id,t.enum_name,
  RPAD(' ',(LEVEL-1)*4,' ')||t.enum_name format1,
  (CASE (LEVEL-1) WHEN 0 THEN '  ' ELSE (LEVEL-1)||'.' END)||LEVEL||' '||t.enum_name format2
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id
ORDER SIBLINGS BY t.enum_id;

结果:

   LVL    ENUM_ID PARENT_ENUM_ID ENUM_NAME             FORMAT1                          FORMAT2
------ ---------- -------------- --------------------- -------------------------------- ---------------------------
     1          1              0 软件部                软件部                             1 软件部
     2          7              1 开发部                    开发部                       1.2 开发部
     3         14              7 研发一部                      研发一部                 2.3 研发一部
     3         15              7 研发二部                      研发二部                 2.3 研发二部
     3         16              7 研发三部                      研发三部                 2.3 研发三部
     3         17              7 测试部                        测试部                   2.3 测试部
     2          8              1 工程部                    工程部                       1.2 工程部
     3         18              8 实施一部                      实施一部                 2.3 实施一部
     3         19              8 实施二部                      实施二部                 2.3 实施二部

示例 6,查询数据的层次数:

SELECT COUNT(DISTINCT LEVEL) depth
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id;

示例 7,查询各级部门的人数及工资之和:

SELECT t3.root_dept_code dept_code,t3.root_dept_name dept_name,
  COUNT(DISTINCT staff_id) cnt_staff,SUM(t3.salary) sum_salary 
FROM(
  SELECT CONNECT_BY_ROOT t1.enum_code root_dept_code,CONNECT_BY_ROOT t1.enum_name root_dept_name,
    t2.staff_id,NVL(t2.base_salary+t2.post_salary,0) salary
  FROM demo.t_field_enum t1
  LEFT JOIN demo.t_staff t2 ON t1.enum_code=t2.dept_code
  WHERE t1.field_code='DEPT'
  CONNECT BY PRIOR t1.enum_id=t1.parent_enum_id
) t3
GROUP BY t3.root_dept_code,t3.root_dept_name
ORDER BY t3.root_dept_code;

结果:

DEPT_CODE                                 DEPT_NAME                                      CNT_STAFF SUM_SALARY
----------------------------------------- --------------------------------------------- ---------- ----------
010000                                    软件部                                                14      99700
010100                                    开发部                                                10      73700
010101                                    研发一部                                               4      29850
010102                                    研发二部                                               2      18500
010103                                    研发三部                                               2      12850
010104                                    测试部                                                 2      12500
010200                                    工程部                                                 4      26000
010201                                    实施一部                                               3      18000
010202                                    实施二部                                               1       8000
020000                                    数据部                                                 0          0
030000                                    市场部                                                 0          0
040000                                    销售部                                                 0          0
050000                                    人事部                                                 0          0
060000                                    财务部                                                 0          0

2.使用方式

以上两个图只是说明概念,与实际输出结果无直接关系。

层次查询示例:

2.3、Oracle 11g 新增的 PIVOT/UNPIVOT

在 Oracle 11g 中新增了两个行列转换函数,分别是行转列函数 PIVOT
和列转行函数 UNPIVOT。我在 11g R2
中测试了一下,感觉还蛮好用的,下面给出我测试的两个 demo。

测试 1,通过 PIVOT 函数实现列转行:

WITH t AS(
  SELECT 1 cid,'苹果' cname, 2000 output FROM DUAL UNION ALL
  SELECT 2 cid,'苹果' cname, 7000 output FROM DUAL UNION ALL
  SELECT 3 cid,'香蕉' cname, 1000 output FROM DUAL UNION ALL
  SELECT 4 cid,'香蕉' cname, 6000 output FROM DUAL UNION ALL
  SELECT 5 cid,'橘子' cname, 5000 output FROM DUAL UNION ALL
  SELECT 6 cid,'西瓜' cname, 8000 output FROM DUAL
)
SELECT * FROM(SELECT t.cname,t.output FROM t) 
  PIVOT(SUM(output) FOR cname IN('苹果' A,'香蕉' B,'橘子' C));

结果:

         A          B          C
---------- ---------- ----------
      9000       7000       5000

测试 2,通过 UNPIVOT 函数实现行转列:

WITH t AS(
  SELECT 1 cid,'苹果' cname, 2000 q1, 4000 q2, 6000 q3, 8000 q4 FROM DUAL UNION ALL
  SELECT 2 cid,'香蕉' cname, 1000 q1, 3000 q2, 5000 q3, 7000 q4 FROM DUAL UNION ALL
  SELECT 3 cid,'橘子' cname, 2000 q1, 5000 q2, 3000 q3, 1000 q4 FROM DUAL UNION ALL
  SELECT 4 cid,'西瓜' cname, 1000 q1, 9000 q2, 1000 q3, 1000 q4 FROM DUAL
)
SELECT cname,quarter,output FROM t UNPIVOT(output FOR quarter IN(q1, q2, q3));

结果:

CNAME QUARTER     OUTPUT
----- ------- ----------
苹果  Q1            2000
苹果  Q2            4000
苹果  Q3            6000
香蕉  Q1            1000
香蕉  Q2            3000
香蕉  Q3            5000
橘子  Q1            2000
橘子  Q2            5000
橘子  Q3            3000
西瓜  Q1            1000
西瓜  Q2            9000
西瓜  Q3            1000

想要进一步了解 PIVOT/UNPIVOT 函数的朋友请参考:pivot and unpivot
queries in
11g,尽管不是官方资料,但比官方资料写的要好的多。

2.2实现树状查询结果

create table DEP( DEPID   number(10) not null, DEPNAME  varchar2(256), UPPERDEPID number(10))---------------------------------------------------------------------INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);---------------------------------------------------------------------SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"FROM DEPSTART WITH UPPERDEPID IS NULLCONNECT BY PRIOR DEPID = UPPERDEPID;

图片 3

DEMO下载

 

 

通过这个例子可以看出,RSF能够实现层次查询中的功能,虽然从语法上来看,比层次查询要复杂一些。

 

2.2、WMSYS.WM_CONCAT

WMSYS.WM_CONCAT 是 Oracle 未公开的一个函数,这意味着在今后的 Oracle
版本中也许将不再可用。官方文档中也完全查不到该函数的任何蛛丝马迹,按常理来说,应该要慎用该函数才是。但据我近几年用
Oracle 以来的观察,似乎每个人都知道 Oracle 中有个 WM_CONCAT
函数,个中缘由我相信用过的人自会知晓。WMSYS.WM_CONCAT
既可用作聚合函数,也可用作分析函数,它的返回值是以逗号分隔的字符串,由满足条件的数据以逗号分隔符串联组成。

示例 1,做聚合函数(无法确保字符串的拼接顺序):

SELECT t.dept_code,WMSYS.WM_CONCAT(t.staff_name) staff_name_all FROM demo.t_staff t GROUP BY t.dept_code;

结果:

DEPT_CODE                              STAFF_NAME_ALL
-------------------------------------- -------------------------------------------------------------------
010101                                 大海,小明,小强,李阳,王二,大国,小强,王二,小明
010102                                 小林,小林,小萨,小萨
010103                                 韩三,小玲,韩三,小玲
010104                                 小梅,小梅,小燕,小燕
010201                                 小军,小芳,小军,小红,小芳,小红
010202                                 小飞,小飞,徐来

示例 2,做分析函数(可确保字符串按指定顺序拼接):

WITH t2 AS(
  SELECT t.dept_code,
  WMSYS.WM_CONCAT(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) post_salary_all
  FROM demo.t_staff t
)
SELECT t2.dept_code,MAX(t2.post_salary_all) post_salary_all FROM t2 GROUP BY t2.dept_code;

结果:

DEPT_CODE                               POST_SALARY_ALL
--------------------------------------- ----------------------------------------------------------------------
010101                                  1850,1850,3500,4000,6000,6000,8000,8000,8000
010102                                  6000,6000,7500,7500
010103                                  2800,2900,5050,5050
010104                                  3000,3100,4500,4600
010201                                  2000,2100,2500,2600,6000,6000
010202                                  5200,5500,5500

1.概述

Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询

 

图片 4

  2  from emp e,emp b

1、层次查询语句

从RSF实现的代码中可以看出:

语法格式:

1.1、CONNECT BY 语法

基本语法:

[ START WITH condition ] CONNECT BY [ NOCYCLE ] condition

查询语法:

SELECT [LEVEL] [CONNECT_BY_ROOT column] [CONNECT_BY_ISLEAF] [CONNECT_BY_ISCYCLE] column(s),expr(s)
FROM table(s)
[WHERE condition(s)]
[START WITH condition(s)]
CONNECT BY [NOCYCLE] [{ PRIOR column1 = column2 | column1 = PRIOR column2 }]
[ORDER [SIBLINGS] BY column(s)]
  • CONNECT
    BY
    :该子句表明每行数据都将按层次顺序检索。在层次查询中,CONNECT BY
    条件中的一个表达式必须由 PRIOR
    运算符限定,从而确定层次结构的父行和子行之间的关系。如果 CONNECT BY
    是复合条件,那么只有一个条件需要 PRIOR,尽管可以有多个 PRIOR。PRIOR
    可出现在比较运算符的任意一侧,从而确定检索数据的顺序是自上而下还是自下而上(上代指跟节点,下代指叶子节点)。如果
    CONNECT BY 的条件是 PRIOR X = Y,那么就会用当前节点的 X
    列的值和其它节点的 Y
    列的值做比较,值相等的节点就会被当作当前节点的子节点,依此类推。如果
    CONNECT BY 后面不加 PRIOR 的话,查询将不进行深层递归。
  • START
    WITH
    :该子句为可选项,用来标识那个节点作为查找树形结构的根节点。层次查询需要确定起始点,START
    WITH 后面可以跟任何合法的条件表达式,还可以使用子查询。如果没有
    START WITH,则每行都是起始点,进而查找其后代节点。
  • LEVEL:一个“伪列”,代表当前节点所在的层级。根节点的 LEVEL 是
    1,根节点的子节点的 LEVEL 是 2,依此类推。
  • CONNECT_BY_ROOT:一个特殊的“伪列”,特殊之处在于它必须与某个字段搭配使用,用于获取根节点记录的字段信息。
  • CONNECT_BY_ISLEAF:一个“伪列”,用于判断当前节点是否为叶子节点,是叶子节点就返回
    1,非叶子节点则返回 0。叶子节点是指没有下级节点的节点。
  • CONNECT_BY_ISCYCLE:一个特殊的“伪列”,用于检查在执行层次查询过程中是否存在死循环,会造成死循环的行返回
    1,其它行则返回 0。该“伪列”只在 CONNECT BY NOCYCLE
    方式下有效,默认情况下,Oracle 在运行时检测到无限循环会直接报错。
  • SIBLINGS:该关键字放在 ORDER BY 中间,即 ORDER SIBLINGS
    BY,用于指定结果集中同一个父节点下各个兄弟节点之间的顺序。这么做的好处是不会破坏数据层次,而如果直接在层次查询语句最后面加上
    ORDER BY,那么整个结果集都将按 ORDER BY
    来排序,这显然会破坏数据层次。

注意:WHERE
条件会限制查询返回的行,但不影响数据的层次关系,不满足条件的节点不返回,但这个不满足条件的节点的后代节点不受此影响。换句话说:WHERE
条件是在遍历结束之后才起作用的,也就是在已经生成的层次数据中进行过滤。

对两种方式实现的结果进行比较,可以发现RSF实现的结果当中会多出一行,并标记为1来表示发生的循环。而使用层次查询时,如果想使用CONNECT_BY_ISCYCLE的话,必须同时使用NOCYCLE,并且是在最下一层节点上标记是否发生了循环。

 

2.1、SYS_CONNECT_BY_PATH

语法:

SYS_CONNECT_BY_PATH(column, char)
  • column:是字符型或能隐式转换成字符型的列,主要作用就是将父节点的路径按照指定的模式出现。
  • char:可以是单字符也可以是多字符,但不能使用列值中包含的字符,且必须是常量,也不允许使用绑定变量。

SYS_CONNECT_BY_PATH 函数就是从 START WITH
开始的地方开始遍历,并记录下其遍历到的节点,START WITH
开始的地方被视为跟节点,将遍历到的路径根据函数中指定的分隔符,组成一个新的字符串。需要注意的是该函数仅在层次查询中有效。示例:

SELECT t.enum_id,t.enum_name,SYS_CONNECT_BY_PATH(t.enum_name,'/') format1,SYS_CONNECT_BY_PATH(t.enum_name,' -> ') format2
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

结果:

    ENUM_ID ENUM_NAME                FORMAT1                               FORMAT2
----------- ------------------------ ------------------------------------- -----------------------------------
          1 软件部                   /软件部                                -> 软件部
          7 开发部                   /软件部/开发部                         -> 软件部 -> 开发部
         14 研发一部                 /软件部/开发部/研发一部                -> 软件部 -> 开发部 -> 研发一部
         15 研发二部                 /软件部/开发部/研发二部                -> 软件部 -> 开发部 -> 研发二部
         16 研发三部                 /软件部/开发部/研发三部                -> 软件部 -> 开发部 -> 研发三部
         17 测试部                   /软件部/开发部/测试部                  -> 软件部 -> 开发部 -> 测试部
          8 工程部                   /软件部/工程部                         -> 软件部 -> 工程部
         18 实施一部                 /软件部/工程部/实施一部                -> 软件部 -> 工程部 -> 实施一部
         19 实施二部                 /软件部/工程部/实施二部                -> 软件部 -> 工程部 -> 实施二部
  • DISTINCT关键字或GROUP BY子句
  • MODEL子句
  • 聚合函数。但是,在SELECT列表中可以使用分析函数
  • 引用query_name的子查询
  • 引用query_name作为右表的外联接

 

3、总结

本文主要讲述了 Oracle 中的层次查询语句 CONNECT BY 及 4
个层次查询函数。以本人的经验来看,掌握这些已经足够应对日常开发中的层次查询需求了。

本文链接
版权声明:本文为博客园博主
韩宗泽
原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!本人初写博客,水平有限,若有不当之处,敬请批评指正,谢谢!

  • 其实内部实际执行的过程还是按照广度优先的方式进行,只是在最终输出结果时,会根据SEARCH子句中指定的方式输出。

 

2、层次查询函数

首先看一个例子:

 

在我们的日常生活中,有很多数据集合都是有层次结构的,如企业的组织架构、政府机构、家族关系等。为满足对层次化数据的查询及格式化等需求,Oracle
从 9i 开始提供可按层次查询的子句和函数。

SELECT LEVEL LV
     , EMPNO
     , LPAD(' ', LEVEL*2-1,' ')||ENAME ENAME
     , MGR
  FROM EMP_RSF
 START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
 ORDER SIBLINGS BY EMPNO;

图片 5

  • 1、层次查询语句
    • 1.1、CONNECT BY
      语法
    • 1.2、CONNECT BY
      示例
  • 2、层次查询函数
    • 2.1、SYS_CONNECT_BY_PATH
    • 2.2、WMSYS.WM_CONCAT
    • 2.3、Oracle 11g 新增的
      PIVOT/UNPIVOT
  • 3、总结

在学习RSF之前,需要先理解如下内容:

 

图片 6

 

RSF的限制条件

2、From之后可以是table,view但是只能是一个table。

层次查询其他功能实现的比较

1、其中level关键字是可选的,表示等级,1表示root,2表示root的child,其他相同的规则。

比较一下使用两种方法实现SYS_CONNECT_BY_PATH的这两段代码。首先不讨论是否简洁,起码从功能上都是一样的。
但是,有一个小差别是,如果在层次查询当中,想取消PATH中第一个:号的话,需要在SYS_CONNECT_BY_PATH前使用LTRIM函数去掉引号,但是使用RSF的话,只需要在定义起点的时候,当第一个引号字符串去掉即可。

 

使用RSF实现层次查询的CONNECT_BY_ISLEAF:

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注