007 SG 注意事项 04

1. Datetime Function ( 前面有相关内容 )

    The hours of the day are measured by the turning of the earth . The time of day at any particular moment depends on where you are.

  时区介绍 : 全球共分为24个时区 , 每个时区占经度15度 , 以本初子午线为中央经线的时区为 零 时区 , 由零时区向东 , 向西各分为12 时区 .  

  地球是自西向东转动 , 东边比西边先看到太阳 , 东边的时间比西边早 , 每个相领时区相差1个小时 , 但是有的国家为了时间统一 , 不是按照

  时区作为时间基础 , 例如 : 中国以北京 ( 东8区 ) 作为统一时间 . ( 本初子午线时区为标准时间 , 其余的时区参考这个时间 , 例如 : 本初的时间

  为 0 时 , 东一区 时间为 -1 , 西一区时间为 1 ,   12, 11,10,9,8,7,6,5,4,3,2,1, 0 , -1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11,-12 。所以中间的时间为基准

  时间 ( standard time ) 其他的是参考时间 ( reference time )  中间基准时间又叫 GMT = UTC ( 0 度经线 ) .

  待进一步确认 ( sg 16 )

2. GROUP BY 中的高级情况 ( ROLLUP , CUBE , GROUPING )

  ROLLUP : 使用在GROUP BY 语句中 , 例如 GROUP BY ROLLUP( A ,B ,C ) , 含义为, 首先正常分组 ( 即按照 A, B, C ) 分组 , 然后自右向左 , 依次减少一个表达式

                     再进行分组 , 上面为 ( A , B , C ), ( A , B) , A , 全部 , 然后返回结果 , 实例如下

                     SELECT WORK_DATE,
                                     SEQ,
                                     VBELN,
                                     SUM(PLANGIQTY1)
                      FROM ZHPPEXP05
                      WHERE WORK_DATE BETWEEN '20070730' AND '20070731'
                      GROUP BY ROLLUP(WORK_DATE,SEQ,VBELN) 

                      结果为 : 

 WORK_DATESEQVBELNSUM(PLANGIQTY1)20070730100000240347192007073010000023921810200707301 152920070730  152920070731100000236974002007073110000023871400200707311 80020070731  800   2329

  CUBE : 使用在GROUP BY 语句中 , 例如 GROUP BY CUBE( A , B, C ) 与 ROLLUP 不同的是 , ROLLUP是有顺序的 , 而CUBE是生成所有表达是的全排列 , 并按照

                全排列进行分组 , 假设表达式为 n , 则就有 2 的 n 次幂个分组 , 例如上例 , 表达式为 ,A , B ,C ,  即 n = 3 , 则就有 8 个分组 , ( ABC , AB , AC , BC , A, B , C , 空 )

                所以 ,实际上  CUBE的分组是包含ROLLUP的分组的 .

  GROUPING FUNCTION :  grouping function can be used in either the CUBE and ROLLUP operator , the grouping function uses a single column as its argument.

                the grouping function must match one of the expressions in the GROUP BY clause . The function returns value of 0 or 1 .

                通过 grouping function 可以判定分组是在哪个级别上的分组 , 如果返回 0 ( 可能是这列参加了分组运算 , 或者是这列根本就存储的是 NULL )

                                                                                                                             如果返回 1 ( 可能是这列没有参加分组运算 , 或者是由于ROLLUP / CUBE 创建出来的 NULL )

                有了这个函数 , 同时比对列与 grouping 函数返回的关系 , 就可以作出判断 . 例如 列有值,但是返回gourping (此列)返回0, 说明这列参与了分组运算 等等 .

  GROUPING SETS : 任意组合的分组函数 , 例如 GROUP BY GROUPING SETS ( (A , B, C), ( B ), (B,C) ) 这种就只是单独的分这三个分组 , 而不会在增加一个全部的分组

                即 汇总一个总数 .  ( 还是这种 GROUPING SETS 函数好 )

  COMPOSITE COLUMN : 组合列 , 即可以将多个列组合成 1个, 作为一个单位出现在分组函数中 , 例如 : ROLLUP( A , (B,C) ) 此时 , BC就做为同一组 , 就好象同一列一样操作.

  3. 高级子查询

  一次性返回多列的子查询 ( 一起返回 , 分开返回 ) 如下例子:

    一起返回 : SELECT employee_id , manager_id , department_id

                     FROM employees

                     WHERE ( manager_id , department_id ) IN

                                     ( SELECT manager_id , department_id

                                       FROM employees

                                       WHERE employee_id IN ( 178, 174 ) )

                     AND employee_id NOT IN ( 178 , 174 ) ;

    分开返回 : SELECT employee_id , manager_id , department_id

                     FROM employees

                     WHERE manager_id IN

                                                                  ( SELECT manager_id

                                                                    FROM employees

                                                                    WHERE employee_id IN ( 174 , 141 ))

                                     department_id IN

                                                                   ( SELECT department_id

                                                                      FROM     employees

                                                                      WHERE employee_id IN ( 174,141))

                   AND employee_id NOT IN ( 174 , 141 ) ;

  inner view : 其实就是 SELECT 字句放在 FROM 之后 , 一般用做连接多个表时

  标量子查询 : 即返回单值量的字查询

  可以使用的地方 :  Condition and expression part of DECODE and CASE ,

                                  All clauses of SELECT except GROUP BY

                                  In the left-hand side of the operator in the SET clause and WHERE clause of UPDATE statement

  不可以使用的地方 : As default values for columns and hash expressions for clusters

                                     In the RETURNING clause of DML statements

                                     As the basic of a function-based index

                                     In GROUP BY clauses , CHECK constraints , WHEN conditions

                                     HAVING clauses

                                     In START WITH and CONNECT BY clauses

                                     In statements that are unrelated to queries , such as CREATE PROFILE

  相关子查询 ( Correlated Subqueries )

  Correlated subqueries are used for row-by-row processing , Each subquery is executed once for every row of the outer query .

  Execution

    Get a candidate row ( fetched by the outer query )

    Execute the inner query using the value of the candidate row ( from outer query )

    Use the values resulting from the inner query to qualify or disqualify the candidate .

    Repeat until no candidate row remains .

    SELECT column1 , column2

    FROM table1 outer                          --> 有时外部表和内部表是一个表, 所以使用别名好一点 , 必须的用

    WHERE column1 operator

                                                     ( SELECT column1 , column2

                                                        FROM table2

                                                        WHERE expr1 = outer.expr2 ) ;

    It is used whenever a subquery must return a different result or set of results for each  candidate row considered by the main query .

  使用 EXISTS ( 快 , 只是逻辑判断 , true or false , 并不一定非的返回某个确定的列 ) ( 可以使用 in 替换 )

    The EXISTS operator tests for existence of rows in the resuluts set of the subquery .

      if a subquery row value is found : ( The search does not continue in the inner query , The condition is flagged TRUE )

      if a subquery row value is not found : ( The condition is flagged FALSE , The search continues in the inner query )

 修改操作 ( 利用相关子查询 )    在别的表也存在

  UPDATE table1 别名1

  SET column = ( SELECT expression

                               FROM table2 别名2 

                               WHERE 别名1.column = 别名2.column ) ;

 删除操作 ( 利用相关子查询 ) 在别的表也存在

  DELETE FROM table1 别名1

  WHERE column operator ( SELECT expression

                                                   FROM table2 别名2

                                                   WHERE 别名1.column = 别名2.column ) ;

  WITH Clause

  Using the WITH clause , you can use the same query block in a SELECT statement when it occurs more that once within a complex query .

  The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace .

  The WITH clause improves performance .

  ( Makes the query easy to read , Evaluates a clause only once , even if it appears multiple times in the query , thereby enhancing performance )

  Using the WITH clause , you can define a query block before using it in a query .

  WITH
  plan_qty AS (
         SELECT WORK_DATE ,SUM(PLANGIQTY1) AS TOTAL_QTY FROM ZHPPEXP05
         WHERE WORK_DATE BETWEEN '20070730' AND '20070810'
         GROUP BY WORK_DATE ) ,
  avg_qty AS (
        SELECT SUM(PLANGIQTY1)/COUNT(*) AS AVERAGE_QTY FROM ZHPPEXP05 )                
  SELECT * FROM plan_qty
  WHERE TOTAL_QTY > ( SELECT AVERAGE_QTY FROM avg_qty )

4.Hierarchical retrieval

  A relational database does not store records in a hierarchical way , however , where a hierarchical relationship exists between the rows of a single table .

  SELECT [ LEVEL ] , column , expr...

  FROM table

  [ WHERE conditions ]

  [ START WITH conditions ]              --> ( True ), If the START WITH clause is omitted , the tree walk is started with all of the rows in the table as root rows .

                                                                      This no longer reflects a true hierarchy .

  [ CONNECT BY PRIOR conditions ]  --> CONNECT BY PRIOR column1 = column2  ( 例如 : CONNECT BY PRIOR employee_id = manager_id )

                                                                         Top down -> column1 = Parent Key , column2 = Child key

                                                                         Bottom up -> column1 = Child Key , column2 = Parent Key

                                                                         ( 可以加 condition 来限制 分支 )
  结合以下例子:总结如下 : CONNECT BY PRIOR 中 , PROIR 是优先的意思 ( 例如 CONNECT BY PRIOR EMPNO = MGR , 则表示 上一行的EMPNO = 下一行的MGR )

                                              ( CONNECT BY PRIOR MGR = EMPNO , 则表示上一行的 MGR = 下一行的 EMPNO , 再结合语意 , 想是什么样的树 )

 

1某一个公司的人员结构如下；

                         董事长（小董）

     ------------------------|-----------------------

      技术经理（小技）             销售经理(小售)

      ------|--------                          ------|-------

        张三   李四                        刘五      周六 

  2创建员工表并插入该公司员工信息记录

    create table EMP
(
  EMPNO NUMBER(10),//员工号
  ENAME VARCHAR2(16),//员工姓名
  MGR   NUMBER(10)//该员工上司的员工号

)

该公司的员工信息在此表中的记录如下：

EMPNO           ENAME      MGR

1                        小董         

2                        小技           1      

3                        小售           1

4                        张三           2  

5                        李四           2

6                        刘五          3

7                        周六          3

3 然后就可以对递归查询实验了

 

SELECT substr(sys_connect_by_path(ENAME,'->'),3)  EMPLOYEE  FROM EMP
CONNECT BY  PRIOR EMPNO=  MGR

START WITH ENAME='小董'

查询结果如下：

小董

小董->小技

小董->小技->张三

小董->小技->李四

小董->小售

小董->小售->刘五

小董->小售->周六 

 直观现象：以【小董】为根节点向下遍历所有分支

SELECT substr(sys_connect_by_path(ENAME,'->'),3)  EMPLOYEE  FROM EMP
CONNECT BY   EMPNO=  PRIOR  MGR

START WITH ENAME='张三"

查询结果如下：

张三

张三->小技

张三->小技->小董

 直观现象：以【张三】为根节点向上遍历，且最后只得到一条分支

总结如下：

 在此例中EMPNO为子节点端，MGR为父节点端

1）prior放在子节点端，则表示扫描树是以start with指定的节点作为根节点从上往下扫描。可能对应一个或多个分支。
start with可以省略，如果省略，表示对所有节点都当成根节点分别进行遍历
2）prior放在父节点端，则表示扫描树是以start with指定的节点作为最低层子节点，从下往上扫描。顺序是子节点往父节点扫描，直到根节点为止，这种情况只能得到一个分支。
start with可以省略，如果省略，表示对所有节点都当成最低层子节点分别往根节点方向遍历

 connect by prior emp = mgr ( 这行的 emp 是下行的 mgr ) 向下

 connect by prior mgr = emp ( 这行的 mgr 是下行的 emp ) 向上

这个要 start with 和 prior 结合着看，依照上面的例子, start with 是从 小董开始，connect prior empno = mgr , 这表示，empno优先，所以小董的 empno 将作为 root 并且优先, 于是找到该 empno的 mgr , 再逐层向下。如果同样的 start with 是从 小董开始，但是 connect prior mgr = empno , 这表示，mgr 优先，则表示 以小董为 root ，并且用小董的mgr 作为传递内容，传递下去，而小董的mgr 为null , 所以最后就只返回根，小董，

宗上， 有了 startwith ， 根就定了。关键是下边的连接问题。所谓层次，只是出现的行的上下

此外，如果想删除某个分支，则可以把删除条件写到CONNECT BY 子句中：

例如：SELECT substr(sys_connect_by_path(ENAME,'->'),3)  EMPLOYEE
FROM EMP 

CONNECT BY   PRIOR    EMPNO=MGR  and    ENAME!='小售 '

START WITH ENAME='小董'

 

连接 树查询与其他表

按等级显示员工的姓名和其所属部门的名称

select e1.ename,d.deptname
from  dept d,(select ename, empno,deptno from emp e connect by mgr=prior empno start with ename='小董)  e1
where  e1.deptno=d.deptno

 

这种分级结构的，最关键就在于后两句话，即 connect by prior empno = mgr, 和 start with ename = ' 小售 ' 那么怎么样去理解这两句话呢， 首先看 start with 表示从哪开始，

这个是开始的条件，找到 ename = ' 小售 ' 这条记录，那么这就是开始了，那么接下来怎么循环下去的，就要看 connect by prior empno = mgr, 就是 上一条记录的 empno = 下一条记录的 mgr, 因为 prior 在 empno 这边.