Oracle11G-SQL开发指南-7-高级查询

1.  使用集合操作符
    注: 使用前的限制条件是：所有查询返回的列数以及列的类型必须匹配，列名可以不同。
    union all 返回各个查询检索出的所有行，包括重复行
    union 返回各个查询检索出的所有行，不包括重复行
    intersect 返回两个查询检索出的共有行
    minus 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行


    t1[1,2,3,4] t2[1,2,5,6]
    select id from t1 union all select id from  t2 ;  -- 结果：1,2,3,4,1,2,5,6
    select id from t1 union select id from  t2 ;      -- 结果：1,2,3,4,5,6
    select id from t1 intersect select id from t2;    -- 结果：1,2
    select id from t1 minus  select id from  t2 ;     -- 结果：3,4
    select id from t2 minus  select id from  t1 ;     -- 结果：5,6


2.  使用translate函数
    translate(x, from_string, to_string):在x中查找from_string字符，并将其转换成to_string对应的字符；
    例：
    select translate('zabcd1234%','abcdefghigklmn1234567890%','cdefghigklmn1234567890ab*') from dual;  -- 结果：zcdef3456*


3.  使用DECODE()函数
    DECODE(value, search_value, result, default_value): 将value与search_value进行比较，相等则返回result，否则返回default_value；
    decode()允许在sql中执行if-then-else类型的逻辑处理，而不需要使用pl/sql.
    decode()的每个参数都可以是列、字面值、函数或子查询。 
     select decode(1,1,2,3) from dual;    -- 结果：2
     select decode(1,2,3,4) from dual;    -- 结果：4


4.  使用CASE表达式
    在sql中实现了if-then-else类型逻辑。工作方式与DECODE()函数类似，推荐使用。
    1> 简单CASE表达式：
        CASE search_expression
   WHEN expression1 THEN result1
   WHEN expression2 THEN result2
   ...
            WHEN expressionN THEN resultN
   ELSE default_result
END;
    2> 搜索CASE表达式：
        CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  WHEN conditionN THEN resultN
  ELSE default_result
        END;


5.  层次化查询     根节点、 父节点、 子节点、 叶节点
    SELECT [LEVEL], column, expression,...
    FROM table
    [WHERE where_clause]
    [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
    说明： 
        a>: LEVEL 是一个伪列，代表树的第几层，对于根节点则返回1，根节点的子节点则返回2
        b>: start_condition 定义层次化查询的起点，
c>: prior_condition 定义父行与子行之间的关系。


    例1：使用START WITH 和 CONNECT BY PRIOR 子句
     SELECT employee_id, manager_id, first_name, last_name
     FROM MORE_EMPLOYEES
     START WITH employee_id = 1
     CONNECT BY PRIOR employee_id = manager_id;


    例2：使用伪例LEVEL
      SELECT LEVEL,employee_id, manager_id, first_name, last_name
        FROM MORE_EMPLOYEES
        START WITH employee_id = 1
      CONNECT BY PRIOR employee_id = manager_id
        ORDER BY LEVEL;


    例3：使用count和levle来获得树中的层次数
     SELECT COUNT(DISTINCT LEVEL)
       FROM MORE_EMPLOYEES
      START WITH EMPLOYEE_ID = 1
     CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;


    例4： 格式化层次查询结果
     SELECT LEVEL, LPAD('  ',2*LEVEL -1 )|| first_name || ' '|| last_name AS employee
       FROM More_Employees
      START WITH employee_id = 1 
    CONNECT BY PRIOR employee_id = manager_id;


    例5： 从非根节点开始遍历
    SELECT LEVEL, LPAD('  ',2*LEVEL -1 )|| first_name || ' '|| last_name AS employee
      FROM More_Employees
     START WITH last_name = 'Jones'
   CONNECT BY PRIOR employee_id = manager_id;


    例6： 从层次查询中删除单一节点（其从属子节点不删除）
     SELECT LEVEL, LPAD('  ',2*LEVEL -1 )|| first_name || ' '|| last_name AS employee
       FROM More_Employees
      WHERE last_name != 'Johson'
      START WITH employee_id = 1
    CONNECT BY PRIOR employee_id = manager_id;




    例7： 从层次查询中删除节点和分支（其从属子节点也删除）
     SELECT LEVEL, LPAD('  ',2*LEVEL -1 )|| first_name || ' '|| last_name AS employee
       FROM More_Employees
      START WITH employee_id = 1
    CONNECT BY PRIOR employee_id = manager_id
        AND last_name != 'Johson';




    例8，在层次化查询中加上条件
     SELECT LEVEL, LPAD('  ',2*LEVEL -1 )|| first_name || ' '|| last_name AS employee,salary
       FROM More_Employees
      WHERE salary < 50000
      START WITH employee_id = 1
    CONNECT BY PRIOR employee_id = manager_id;


6.使用扩展的GROUP BY 子句
    ROLLUP > 为每个分组返回小计记录以及为所有分组返回总计记录
             Rollup(a,b,c)==》 (a,b,c),(a,b),(a),(全表)
               1> 分组的列，用括号括起来; 
      2> 最好加上ORDER BY子句，防止改变默认顺序；
      3> 传递多列时，会将这些记录分组成列值相同的块，按第一个列名参数分组统计，最后在来一个总的
      4> 也可以与其他聚合函数一起使用；
      5> 效率比起来Group By + Union组合方法效率高得多


    CUBE >   返回每一列组合的小计记录，同时在末尾加上总计记录
             cube(a,b,c)==》 (a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)


    GROUPING > 接受一列返回0或1，如果列值为空则返回1；例值非空则返回0,只能用在ROLLUP和CUBE查询中使用
               常用在返回空值的地方来显示某个值时；
      常与 case .. when.. then ..end 使用；比如：case grouping(id) when 1 then 'all' else id end ;


    GROUPING SETS子句 > 只返回小计记录
               grouping sets(a,b) ==> (a),(b)
      性能比CUBE好。
      查出的结果项少；
    
    GROUPING_ID > 可以接受一列或多列，返回GROUPING位向量(0,1的组合)的十进制值。
                  常用于和HAVING子句结合来过滤记录


    GROUP_ID > 不接受任何参数，用来消除GROUP BY 子句返回的重复记录，值为0~ n-1次，第一次是0， 第二次是1。。。


    可以在GROUP BY 子句中多次使用一个列： GROUP BY id1 , ROLLUP(id1,id2) ,这样的结果是id1结果重复，可结合GROUP_ID使用


    例：
    SELECT DIVISION_ID, SUM(SALARY) FROM EMPLOYEES2 GROUP BY DIVISION_ID ORDER BY DIVISION_ID;


    SELECT DIVISION_ID, SUM(SALARY) FROM EMPLOYEES2 GROUP BY ROLLUP (DIVISION_ID) ORDER BY DIVISION_ID;
    等同于：
    SELECT DIVISION_ID, SUM(SALARY) FROM EMPLOYEES2 GROUP BY DIVISION_ID 
    union all 
    SELECT null, SUM(SALARY) FROM EMPLOYEES2    
    ORDER BY 1 ;


    SELECT DIVISION_ID,job_id,SUM(SALARY) FROM EMPLOYEES2 GROUP BY ROLLUP (DIVISION_ID，job_id) ORDER BY DIVISION_ID,job_id;
    等同于：
    SELECT DIVISION_ID,job_id,SUM(SALARY) FROM EMPLOYEES2 GROUP BY ROLLUP (DIVISION_ID，job_id) 
    union all
    SELECT DIVISION_ID,null, SUM(SALARY) FROM EMPLOYEES2 GROUP BY DIVISION_ID 
    union all 
    SELECT null, SUM(SALARY) FROM EMPLOYEES2    
    ORDER BY 1 ;


7.  使用分析函数
    1> 评级函数:用于计算等级、百分点、n分片等。
       RANK(): 返回数据项在分组中的排名，排名相等的情况(并列)会在名次中留下空位(跳过一个名次)
       DENSE_RANK():返回数据项在分组中的排名，排名相等的情况(并列)会在名次中不留下空位(不跳过一个名次)
       ROW_NUMBER():为每一条分组记录返回一个数字，不并列


       CUME_DIST():返回特定值相对于一组的位置，
                   计算结果为相对位置/总行数。返回值(0,1]。
                   例如在一个5行的组中，返回的累计分布值为0.2,0.4,0.6,0.8,1.0;
                   注意对于重复行，计算时取重复行中的最后一行的位置。
  a)作为聚合函数的用法
                       语法：cume_dist(expr) with group (order by exp)
                       cume_dist()用参数中的指定的数据构造一条假定的数据并插入到现存行中，
                       然后计算这条假定数据在所有行中的相对位置。
  b)作为分析函数的用法
                       语法：cume_dist() over([partition_clause] order_by_clause)


       PERCENT_RANK():返回某个值相对于一组值的百分比排名
                      和cume_dist的不同点在于计算分布结果的方法。
                      计算方法为 (相对位置-1)/(总行数-1),
                      因此第一行的结果为0。返回值[0,1]。
                      例如在一个5行的组中，返回的累计分布值为0,0.25,0.5,0.75,1.0;
                      注意对于重复行，计算时取重复行中的第一行的位置。


                      a)作为聚合函数的用法
                          语法：percent_rank(expr) with group (order by exp)
                      b)作为分析函数的用法
                          语法： percent_rank() over([partition_clause] order_by_clause)


        PERCENTILE_DISC():返回一个与输入的分布百分比值相对应的数据值，
                          分布百分比的计算方法同函数CUME_DIST()，
                          如果没有正好对应的数据值，就取大于该分布值的下一个值。
                      a)作为聚合函数的用法
                          语法： percentile_disc(expr) with group (order by expr)
                      b)作为分析函数的用法
                          语法：percentile_disc(expr) with group (order by expr) over([partition_clause])


RANK（）在出现等级相同的元素时预留为空，DENSE_RANK()不会。
Eg：某产品类型有两个并列第一


RANK()：第一二为1，第三位3


DENSE_RANK()：第一二为1，第三位2


1 SELECT
2 column_name,
3 RANK() OVER （ORDER BY column_name DESC） AS rank,
4 DENSE_RANK() OVER （ORDER BY SUM(column_name) DESC） AS dense_rank
5 FROM table_name
OVER 需要，括号内为编号顺序
注意：order by 时，desc NULL 值排在首位，ASC时NULL值排在末尾


可以通过NULLS LAST、NULLS FIRST 控制


RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序


RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样，就会按照month 来分，即所需要排列的信息先以month 的值来分组，在分组中排序，各个分组间不干涉


ROLLUP、CUBE、GROUP SETS （只显示小计信息） 与 RANK() 结合使用


 CUME_DIST()、PERCENT_RANK()
反百分比函数：PERCENTILE_DISC(x), PERCENTILE_CONT(x)


NTILE()


ROW_NUMBER()
ROW_NUMBER()从1开始，为每条记录返回一个数字


SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;


    反百分点函数
    窗口函数
    报表函数
    延迟与领先函数
    首函数与末函数
    线性回归函数
    假想评级及分布函数