高级sql学习--增强group by！！！

总结：
1.提示当使用group by时，未在group by部分用到的表列在select部分出现时必须使用分组函数。
2.having子句允许用户指定对一个记录组的搜索条件。而通常的where查询条件只针对单记录，不针对记录组。
3.在Group By子句中，必须采用表达式的全称，而不能够采用其别名.
4.采用Group语句会自动对纪录进行排序。在使用Order By排序子句对统计结果进行重新排序的时候，要慎重。
5.如果想把某个字段当作第一顺序排序的话，则只需要把这个字段放置在分组语句中的第一个参数即可。

 

示例：
--聚合函数，不能出现在where子句中，比如where avg(salary)>4000 select t.deptno,sum(t.sal) from emp t group by t.deptno having sum(t.sal)>9000; DEPTNO SUM(T.SAL) ------ ---------- 30 9400 20 10875

--查出表中name重复大于10条的记录 select name from test group by name having count(*)>10;

--Rollup分组产生的结果集包含常规的分组和小计行。在生成包含小计和合计的报表时，ROLLUP 运算符很有用。 --使用grouping函数标识rollup或者cube创建的行 --用GROUPING函数模拟能够发现在一行中的构成小计的分组,返回0或者1。 --GROUPING函数返回0意味着下列情况：这个表达式包含在小计中. --GROUPING函数返回1意味着下列情况：这个表达式没有包含在小计中. select decode(grouping(t.deptno),1,'10/20/30',t.deptno) deptno, case when grouping(t.ename)=1 and grouping(t.deptno)=0 then '小计' when grouping(t.ename)=1 and grouping(t.deptno)=1 then '合计' else t.ename end ename, sum(t.sal) sal from emp t group by rollup(t.deptno,t.ename); DEPTNO ENAME SAL ---------------------------------------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 10 小计 8750 20 FORD 3000 20 ADAMS 1100 20 JONES 2975 20 SCOTT 3000 20 SMITH 800 20 小计 10875 30 WARD 1250 30 ALLEN 1600 30 BLAKE 2850 30 JAMES 950 30 MARTIN 1250 30 TURNER 1500 30 小计 9400 10/20/30 合计 29025

 

--Cube操作依据条件中列的所有可能组合情况进行分组，并对每一个分组返回一行汇总。CUBE是一个类似ROLLUP的扩展，使得可以用一个语句计算所有可能的聚合。 select t.deptno,sum(t.sal) sal from emp t group by cube (t.deptno); DEPTNO SAL ------ ---------- 29025 10 8750 20 10875 30 9400

--使用grouping sets产生一个单独的结果集（等价于union all的方式） --GROUPING SETS是GROUP BY子句的扩展，可以在一个查询中定义多个组。 --Oracle服务器计算所有在GROUPING SETS子句中指定的组，并且把每个单独的组产生的结果进行UNION ALL操作。 --GROUPING SET的效率：只需要一次全表扫描，不需要写复杂的UNION语句，同时GROUPING SETS子句中含有的元素越多，得到的性能增益越大。 select t.deptno,t.ename,t.job from emp t group by grouping sets((t.deptno,t.ename),(t.deptno,t.job)); DEPTNO ENAME JOB ------ ---------- --------- 20 JONES 30 WARD 20 SCOTT 10 KING 30 JAMES 30 ALLEN 30 MARTIN 30 BLAKE 20 FORD 20 SMITH 20 ADAMS 10 MILLER 10 CLARK 30 TURNER 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST select t.deptno,t.ename from emp t group by (t.deptno,t.ename); DEPTNO ENAME ------ ---------- 20 JONES 30 WARD 20 SCOTT 10 KING 30 JAMES 30 ALLEN 30 MARTIN 30 BLAKE 20 FORD 20 SMITH 20 ADAMS 10 MILLER 10 CLARK 30 TURNER select t.deptno,t.job from emp t group by (t.deptno,t.job); DEPTNO JOB ------ --------- 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST