ORACLE的分组统计查询

分组统计操作，是比较重点
大部分学生在这里出现问题，为什么会存在分组？

以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