oracle 高级查询

7、高级查询
    7.1:UNION ALL：返回查询检索出的所有行，包括重复的行
    7.2:UNION：返回查询检索出的所有非重复行。
    7.3:INTERSECT：返回查询检查出来的共有行。
    7.4:MINUS：返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行。
    7.5:GROUP BY 之ROLLUP，可以为每个分组返回小计记录以及为所有分组返回总计记录。
         eg:
  select divid,sum(salary) from employee group by divid order by divid;
         DIV  SUM(SALARY)
  ----  ----------
  bus   1610000
  ope   1320000
  sal     4936000
  sup    1015000

  向ROLLUP传递一列
         eg:
  select divid,sum(salary) from employee group by rollup(divid) order by divid;
         DIV  SUM(SALARY)
  ----  ----------
  bus   1610000
  ope   1320000
  sal     4936000
  sup    1015000
            8881000
    
         向ROLLUP传递多列
  eg:
         select divid,jobid,sum(salary) from employee group by rollup(divid,jobid) order by divid,jobid;
  DIV     JOB     SUM(SALARY)
  -----   ------   -------------------
  BUS    MGR    530000
  BUS    PRE      800000
  BUS    WOR    180000
  BUS               1610000
  OPE    ENG      240000
  OPE    MGR      805000
  OPE    WOR      270000
  OPE                 1320000
  SAL     MGR      4446000
  SAL     WOR        490000
  SAL                   1936000
  SUP    MGR         465000
  SUP    TEC           115000
  SUP     WOR         435000
  SUP                     1015000
                            8881000

          修改传递给ROLLUP的列的位置,则会得到以jobid为准的一组数字统计

    7.6:GROUP BY 之CUBE,返回CUBE中所有列组合的小计信息,同时在最后显示总计信息
    7.7:GROUPING函数
         GROUPING函数可以接受一列,返回0或者1,如果列值为空,那么GROUPING()返回1;如果列值非空,则返回0,GROUPING()只能在使用ROLLUP或CUBE的查询中使用
    7.8:GROUPING SETS子句
         使用GROUPING SETS子句可以只返回小计记录.
  eg:
  select divid,jobid,sum(salary) from employee group by GROUPING SETS(divid,jobid) order by divid,jobid;
  DIV       JOB        SUM(SALARY)
  ------    -------    ---------------------
  BUS                    1610000
  OPE                    1320000
  SAL                     4936000
  SUP                    1015000
                     ENG         245000
       MGR       6246000
       PRE           800000
       TEC           115000
       WOR        1475000
 
       GROUPING SETS子句的性能一般比CUBE好,因此,应该尽可能使用GROUPING SETS,少使用CUBE..

       7.9:GROUPING_ID函数
        GROUPING位向量
 divid           jobid              位向量                  grouping_id返回值
 非空            非空                00                        0
 非空               空                 01                       1
 空                非空                10                       2
 空                   空                11                       3
 /** 位向量是2进制,grouping_id是10进制  */
      
       GROUPING_ID()用武之地在于使用HAVING子句过滤记录,HAVING子句可以将不包含小计或总计的记录除去,这只要简单的检查grouping_id()的返回值,看其大于0就可以实现
       select divid,jobid,grouping_id(divid,jobid) grpId,sum(salary) from employee group by cube(divid,jobid) having grouping_id(divid,jobid) > 0 order by divid,jobid;

       8.0:在GROUP_ID函数
            GROUP_ID函数可用于消除GROUP BY子句返回重复记录,GROUP_ID()不接受任何参数,如果某个特定的分组重复出现N次,那么GROUP_ID返回0到N-1之间的一个整数
     如果出现两次,那么GROUP_ID()函数等于1
     去重
     eg:
     select divid,jobid,group_id(),sum(salary) from employee group by divid,rollup(divid,jobid) having group_id = 0;