Shulou(Shulou.com)06/01 Report--

/ * Oracle Advanced Group * * /

/ *-auther:similarface---*/

/ *-4 February 2015-* /

-- columns used in GROUP BY in oracle must match columns in select statements that do not use aggregate functions

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

SELECTd.dname,COUNT (e.empno)

FROMdeptd,empe

WHEREe.deptno (+) = D.DEPTNO

GROUPBYD.DNAME

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

SELECTd.dname,COUNT (E.EMPNO)

FROMdeptd LEFTOUTERJOINempeONE.DEPTNO=D.DEPTNO

GROUPBYd.dname

The results produced by GROUP BY can be limited by the screening criteria given in the HAVING clause

Demand: from the first full year of having employees, all departments that employ at least 5 employees

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

SELECT D. DNAME discipline TRUNC (e.hiredatescripting yyyyyy`), COUNT (e.empno)

FROMdeptd,empe

WHERE D.DEPTNO=E.DEPTNO

GROUPBYD.DNAME,TRUNC (e.hiredateDictionary yyyyyy')

HAVING COUNT (e.empno) > 5

ANDTRUNC (e.hiredate) BETWEEN (SELECTMIN (hiredate)

FROMemp)

AND (SELECTMAX (hiredate))

FROMemp)

-- CUBE will cause every row to be considered to include all possible combinations of elements in the CUBE parameter

CREATETABLEtestCube

(

Num1 NUMBER

Num2 NUMBER

);

INSERTINTOtestCube

VALUES (1 dint 2)

COMMIT

SELECT*

FROMtestCube

GROUPBYCUBE (num1,num2)

-- GROUPING can exclude null values generated by cube

SELECTDECODE (GROUPING (num1), 1 meme num 1)

DECODE (GROUPING (num2), 1 meme NUM2)

FROMtestCube

GROUPBYCUBE (num1,num2)

-- GROUPING_ID determines which of its parameters is used to generate a hyperaggregate row, and then creates a bit vector to return it numerically.

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), 1GRP BIT1') gb_1

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

FROMtestGroupid

GROUPBYCUBE (bit_1,bit_0))

SELECTgid

Bv_1 | | bv_0 bit_vector

Gb_1

Gb_0

FROMcubed

ORDERBYgid

Limitations of GROUP BY

1. LOB columns, nested tables and arrays cannot use GROUPBY

2. Scalar quantum query expressions are not allowed

3. GROUP BY reference object type columns cannot be parallelized

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.