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行分别是对应着三个汇总条件。