分组统计操作,是比较重点
大部分学生在这里出现问题,为什么会存在分组?
以emp表为例子:在这里job 有重复的内容、deptno也有重复的内容。
只要有重复的内容,就可以做分组
--统计出每个部门的人数
SELECT deptno FROM emp GROUP BY deptno
--分组的基本操作
SELECT job,min(sal),max(sal) FROM emp GROUP BY job
注意:不要光看,写写代码,基本语法要会,特别是数据库的限制
--求出每个部门平均数值,求出最大的。应该怎么写语句
SELECT MAX(avg(sal)) FROM emp GROUP BY deptno --执行顺序:WHERE FROM GROUP SELECT OREDER SELECT deptno , AVG(sal) FROM emp GROUP BY deptno
--查询每个部门的名称、部门人数、平均工资、平均服务年限
--平均年限用到MONTHS_BETWEEN()函数
1.确定所需要的数据表
dept
emp
2.确定已知的关联字段
emp.deptno=dept.deptno
外链接后,40部门就出来了
SELECT t.dname,COUNT(e.empno), ROUND( AVG(e.sal),2) , ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate)/12),2) FROM emp e, dept t where e.deptno(+)=t.deptno GROUP BY t.dname;
--执行顺序:WHERE-》 FROM-》 GROUP-》 SELECT-》 OREDER
统计出领取佣金与不领取佣金的雇员的平均工资,平均服务年限,雇员人数
所需要的表
salgrade
emp:
关联字段:
雇员与工资等级:
SELECT ROUND(AVG(sal),2), ROUND(AVG( MONTHS_BETWEEN(SYSDATE, hiredate)/12 ), 2), COUNT(empno) FROM emp WHERE comm IS NULL union SELECT ROUND(AVG(sal),2), ROUND(AVG( MONTHS_BETWEEN(SYSDATE, hiredate)/12 ), 2), COUNT(empno) FROM emp WHERE comm IS not NULL
---------------------------------------------
HAVING子句
查询出所有平均工资大于2000的职位信息
SELECT job,ROUND(AVG(sal),2),COUNT(EMPNO) FROM emp GROUP BY job HAVING ROUND(AVG(sal),2)>2000 --以下都可以 SELECT job,ROUND(AVG(sal),2),COUNT(EMPNO) FROM emp HAVING ROUND(AVG(sal),2)>2000 GROUP BY job
现在的执行顺序是 FROM WHERE SELECT GROUP HAVING
列出至少有一个 员工的所有部门的编号、名称,并统计出这些 部门的平均工资、最低工资、最高工资
SELECT t.deptno,t.dname,ROUND(AVG(e.sal),2),MIN(e.sal),MAX(e.sal),COUNT(e.empno) FROM emp e,dept t WHERE e.deptno=t.deptno GROUP BY t.deptno,t.dname HAVING COUNT(e.empno)>1
HAVING与WHERE子句的区别:
WHERE 是分组之前使用
HAVING 是分组之后的使用
--简单又综合的范例
显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于5千,输出的结果按月工资的合计升序排列
SELECT e.job,SUM(e.sal) SUM FROM emp e WHERE e.JOB <>'CLERK' GROUP BY JOB HAVING sum(e.sal)>5000 order by SUM(e.sal) 或者以下一样 SELECT e.job,SUM(e.sal) SUM FROM emp e WHERE e.JOB <>'CLERK' GROUP BY JOB HAVING sum(e.sal)>5000 order by SUM