0 order by asc/desc 默认升序
order by 列的名字|表达式|别名|序号
把空放在后边:order by desc nulls last
1分组函数--会自动滤空值
count(*|distinct|clumn) max min sum avg
select sum(comm)/count(*) 一,
sum(comm)/count(comm) 二,avg(comm) 三from emp
2 过滤解决,空值替换函数,NVL(comm,0)
1 select count(*),count(comm)
from emp
2 select count(*),count(nvl(comm,0))
from emp
3 分组;
按部门,不同的职位统计平均薪水
select deptno,job,avg(sal) from emp group by deptno;
以上出错
**Oracle 所有为包含在组函数中都应该包含,group by语句后
4 where和having
如果where 和having都可以适用于group ,可以优先考虑====where
如果条件中含有组函数,则必须适用having
5 Group by 的增强
报表相关结构
group by deptno,job +
group by deptno +
group by null
=====
group by rollup(deptno,job)
-> 通用写法:group by rollup(a,b);
-----------------------------------
6 分段:
break on deptno skip 2
break on null消除
select deptno,job,avg(sal)
from emp
group by rollup(deptno,job) order by deptno
/
原文:http://www.cnblogs.com/foreverzd/p/4087927.html