Oracle高級分組

/******************Oracle 高級分組*****************************/

/*-----------------auther:similarface-----------------------*/

/*--------------------2015年2月4日----------------------------*/

--oracle中GROUP BY中使用的列必須與select語句中沒有使用聚合函數的列相匹配

/* Formatted on 2015/2/4 15:42:12(QP5 v5.256.13226.35538) */

  SELECTd.dname,COUNT(e.empno)

    FROMdeptd,empe

   WHEREe.deptno(+)=D.DEPTNO

GROUPBYD.DNAME;

/* Formatted on 2015/2/4 15:47:45(QP5 v5.256.13226.35538) */

  SELECTd.dname,COUNT(E.EMPNO)

    FROMdeptd LEFTOUTERJOINempeONE.DEPTNO=D.DEPTNO

GROUPBYd.dname;

--GROUP BY 所產生的結果可以通過HAVING子句中所給出的篩選標準來進行限制

--需求:從有雇員以來的第一個完整年開始所有至少雇傭了5名員工的部門

/* Formatted on 2015/2/4 16:22:11(QP5 v5.256.13226.35538) */

SELECT D.DNAME,TRUNC(e.hiredate,'yyyy'),COUNT(e.empno)

FROMdeptd,empe

WHERE D.DEPTNO=E.DEPTNO

GROUPBYD.DNAME,TRUNC(e.hiredate,'yyyy')

  HAVING     COUNT(e.empno)>5

         ANDTRUNC(e.hiredate,'yyyy')BETWEEN(SELECTMIN(hiredate)

                                                  FROMemp)

                                            AND(SELECTMAX(hiredate)

                                                  FROMemp);

--CUBE將會使得對每一行都要考慮包含CUBE參數中所有可能的元素組合

CREATETABLEtestCube

(

   num1   NUMBER,

   num2   NUMBER

);

INSERTINTOtestCube

     VALUES(1,2);

COMMIT;

  SELECT*

    FROMtestCube

GROUPBYCUBE(num1,num2);

--GROUPING可以排除cube生成的空值

  SELECTDECODE(GROUPING(num1),1,'NUM1',num1),

         DECODE(GROUPING(num2),1,'NUM2',num2)

    FROMtestCube

GROUPBYCUBE(num1,num2);

--GROUPING_ID確定其參數中的哪一行被用來生成超聚合行，然后創建一個位矢量以數值返回

WITH testGroupid AS(SELECT1 bit_1,0 bit_0 FROM DUAL),

     cubed

     AS(  SELECTGROUPING_ID(bit_1,bit_0) gid,

                 TO_CHAR(GROUPING(bit_1))bv_1,

                 TO_CHAR(GROUPING(bit_0))bv_0,

                 DECODE(GROUPING(bit_1),1,'GRP BIT1') gb_1,

                 DECODE(GROUPING(bit_0),1,'GRP BIT0') gb_0

             FROMtestGroupid

         GROUPBYCUBE(bit_1,bit_0))

  SELECTgid,

         bv_1||bv_0 bit_vector,

         gb_1,

         gb_0

    FROMcubed

ORDERBYgid;

GROUP BY 局限性

1、LOB列，嵌套表和數組不能使用GROUPBY

2、標量子查詢表達式不允許

3、GROUP BY引用對象類型列不能并行化