oracle中group关键字详解

GROUP子句：（包括：ROLLUP，CUBE，GROUP BY GROUPING SET）

ROLLUP
--说明：每组增加一汇总行，将每组数据分组统计（常用AVG、SUM函数）

--实例：
SELECT DEPTNO,DECODE(JOB),SUM(SAL) FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);
--结果：

CUBE

--实例：
SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
department_name) AS department_name,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY CUBE (department_name, job_id);

--结果：
DEPARTMENT_NAME  JOB_ID   Total Empl  Average Sal
------------------------------   ----------  ---------------------
Accounting   AC_ACCOUNT  1   99600
Accounting   AC_MGR   1  144000
Accounting   All Jobs  2   121800
Administration   AD_ASST  1   52800
.
.
.
All Departments  ST_MAN   5   87360
All Departments  All Jobs  107   77798.1308

GROUP BY GROUPING SET...

--说明：多条件汇总语句

--实例：
SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(sum(amount_sold) , ’9,999,999,999’) SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN (’Direct Sales’, ’Internet’)
AND times.calendar_month_desc IN (’2000-09’, ’2000-10’)
AND co.country_id IN (’UK’, ’US’)
GROUP BY GROUPING SETS(
(channel_desc, calendar_month_desc, co.country_id),
(channel_desc, co.country_id),
( calendar_month_desc, co.country_id) );

--结果：
CHANNEL_DESC  CALENDAR  CO  SALES$
-------------------- -------- -- --------------
Direct Sales  2000-09  UK  1,378,126
Direct Sales 2000-10  UK 1,388,051
Direct Sales  2000-09  US  2,835,557
Direct Sales  2000-10  US  2,908,706
Internet  2000-09  UK  911,739
Internet 2000-10  UK  876,571
Internet  2000-09  US  1,732,240
Internet  2000-10  US  1,893,753
Direct Sales    UK  2,766,177
Direct Sales    US  5,744,263
Internet    UK  1,788,310
Internet    US  3,625,993
  2000-09  UK  2,289,865
  2000-09  US  4,567,797
  2000-10  UK  2,264,622
  2000-10  US  4,802,459
--注：1-8行，9-12行，13-16行分别是对应着三个汇总条件。